Select columns from a table with a space in its name

Question

I'm trying to select columns from two tables, Lignes and Détail Production. Détail Production links to the first one with the key NoLigne (which is the same name in both tables).

I know that I have to put [ ] or `` around the table's name, but I'm having the error No value given for one or more required parameters, which I believe means that SQL doesn't recognize the name. I tried aliasing the name of the table having a space in its name, but I have the same error. Here is my code:

SELECT
    NoProduction,
    Quantite,
    DateMaxProd,
    Lignes.Référence
FROM
    [Détail Production] AS D
INNER JOIN
    Lignes ON D.NoLigne = Lignes.NoLigne
WHERE
    D.Soldee = 0 AND 
    D.EtatLigne = 0 AND
    Lignes.Soldee = 0 AND
    (QteRecue - Quantite - Acompter * NbHS)>0

Unfortunately, I can't get rid of the alias or the name of the table in the FROM and WHERE clause because my tables share columns with the same name. I can't rename the tables or the columns, and I'm actually using the software Windev which uses HFSQL as a dbms. I'm trying to connect to an access database with the OLEDB connector, and when I switch to HFSQL it works.

Here is a mre:

SELECT
  *
FROM
  [Détail Production]
  INNER JOIN
  Lignes ON [Détail Production].NoLigne = Lignes.NoLigne

When using HFSQL database, it works, when using OLEDB with an access database, it throws the error No value given for one or more required parameter

Thanks for your help.

Which dbms are you using? ANSI/ISO SQL has double quotes for delimited identifiers, e.g. `"Detail Production"`. — jarlh, Aug 29 '22 at 15:15
I'm using HFSQL, when using double quotes I get the error `incomplete request clause` — Thibault Abry, Aug 30 '22 at 10:20
`AND (QteRecue - Quantite - Acompter * NbHS)` is missing something. — jarlh, Aug 30 '22 at 10:47

Thibault Abry · Answer 1 · 2022-08-30T13:43:01.267

I found the problem:

SELECT
    D.NoProduction,
    D.Quantite,
    D.DateMaxProd,
    Lignes.Référence
FROM
    [Détail Production] AS D
INNER JOIN
    Lignes ON D.NoLigne = Lignes.NoLigne
WHERE
    D.Soldee = 0 AND 
    D.EtatLigne = 0 AND
    Lignes.Soldee = 0 AND
    (D.QteRecue - D.Quantite - D.Acompter * D.NbHS) > 0

I was missing the comparison on the last condition of the WHERE clause. I thought it was because of the alias or the brackets because others online had similar problems and the error changed as I tried other ways of writing the FROM clause. The right way to write a name with spaces in HFSQL is with brackets [ ]. Also, there were problems with names not matching accents from the database.

Select columns from a table with a space in its name

1 Answers1