How to fix : The multi-part identifier "%.*ls" could not be bound

Question

i'm working on one big query to retriview certain DATA in a SQL Express DB and i'm facing issues while merging two queries.

I already tried to make an inner join on the global second query but since i use a certain GROUP BY for my sum, i didn't work.

Here's my current query :

SELECT 
col.CO_No as nom_commercial,
det.DO_PIECE AS ref_document,
det.AB_NO AS numero_abonnement, 
det.CA_NUM AS compte_analytique,  
det.CBMODIFICATION AS modified_on, 
det.CT_NUMPAYEUR AS ref_societe_payeur,
det.DO_CONTACT AS contact, 
det.DO_COORD01 AS nom_client_final1, 
det.DO_COORD02 AS nom_client_final2,
det.DO_DATE AS date_emission,
det.DO_DEBUTPERIOD AS debut_periode,
det.DO_FINPERIOD AS fin_periode, 
det.DO_REF AS numero_piece_externe,
det.DO_TIERS AS ref_societe_sage, 
det.DO_TotalTTC - det.DO_TotalHTNet AS total_tva,
det.DO_TotalHTNet AS total_ht,
det.DO_TotalTTC AS total_ttc,
det.DO_TYPE AS type_document,
DOC.prix_revient_total,
det.DO_TotalHT - DOC.prix_revient_total as total_marge
FROM F_DOCENTETE as det, F_COLLABORATEUR as col
INNER JOIN
(SELECT 
lg.DO_Piece,
SUM((lg.DL_Qte)*(lg.DL_PrixRU)) as prix_revient_total
FROM F_DOCLIGNE as lg
GROUP BY lg.DO_Piece
) AS DOC
ON det.DO_Piece = DOC.DO_Piece
WHERE det.CO_NO = col.CO_NO AND(det.DO_PIECE LIKE 'BC%')

Made up with these two separated queries : (1)

SELECT
lg.DO_Piece,
SUM((lg.DL_Qte)*(lg.DL_PrixRU)) as prix_revient_total
from F_DOCLIGNE as lg
JOIN F_DOCENTETE
ON ((lg.DO_Piece = F_DOCENTETE.DO_Piece) AND(F_DOCENTETE.DO_PIECE LIKE 'BC%'))
JOIN F_COLLABORATEUR
ON F_DOCENTETE.Co_No = F_COLLABORATEUR.CO_NO
GROUP BY lg.DO_Piece, F_DOCENTETE.DO_Piece

(2)

SELECT 
F_COLLABORATEUR.CO_NOM AS nom_commercial,
F_DOCENTETE.DO_PIECE AS ref_document,
F_DOCENTETE.AB_NO AS numero_abonnement, 
F_DOCENTETE.CA_NUM AS compte_analytique,  
F_DOCENTETE.CBMODIFICATION AS modified_on, 
F_DOCENTETE.CT_NUMPAYEUR AS ref_societe_payeur,
F_DOCENTETE.DO_CONTACT AS contact, 
F_DOCENTETE.DO_COORD01 AS nom_client_final1, 
F_DOCENTETE.DO_COORD02 AS nom_client_final2,
F_DOCENTETE.DO_DATE AS date_emission,
F_DOCENTETE.DO_DEBUTPERIOD AS debut_periode,
F_DOCENTETE.DO_FINPERIOD AS fin_periode, 
F_DOCENTETE.DO_REF AS numero_piece_externe,
F_DOCENTETE.DO_TIERS AS ref_societe_sage, 
F_DOCENTETE.DO_TotalTTC - F_DOCENTETE.DO_TotalHTNet AS total_tva,
F_DOCENTETE.DO_TotalHTNet AS total_ht,
F_DOCENTETE.DO_TotalTTC AS total_ttc,
F_DOCENTETE.DO_TYPE AS type_document
FROM F_DOCENTETE, F_COLLABORATEUR
WHERE F_DOCENTETE.CO_NO = F_COLLABORATEUR.CO_NO AND(F_DOCENTETE.DO_PIECE LIKE 'BC%')

For me, it should just merge both tables on the 'DO_Piece' value (availible in both tables), but it doesn't work and i have this error :

Msg 4104, Level 16, State 1, Line 30 L'identificateur en plusieurs parties "F_DOCENTETE.DO_Piece" ne peut pas être lié. (My Visual Studio is in French)

Translated in English : Msg 4104, Level 16, State 1, Line 30. The multi-part identifier "F_DOCENTETE.DO_Piece" could not be bound.

I'd like to understand why, and how to correct it.

Thanks for your answers.

You really should start using ANSI-92 style joins. They have been around now for more than 25 years. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins — Sean Lange, Jul 18 '19 at 15:54
I really suggest using aliases as well. That SQL is really difficult to tell what is being referenced. — Thom A, Jul 18 '19 at 15:57
I don't really understand why my query looks 'old-style', it doesn't look like the one in the website you sent. — Thomas Lefloch, Jul 19 '19 at 07:44

score 1 · Answer 1 · answered Jul 18 '19 at 16:00

Some aliases and modern joins turns this wall of text into something you can read a lot easier.

SELECT 
    c.CO_No as nom_commercial,
    d.DO_PIECE AS ref_document,
    d.AB_NO AS numero_abonnement, 
    d.CA_NUM AS compte_analytique,  
    d.CBMODIFICATION AS modified_on, 
    d.CT_NUMPAYEUR AS ref_societe_payeur,
    d.DO_CONTACT AS contact, 
    d.DO_COORD01 AS nom_client_final1, 
    d.DO_COORD02 AS nom_client_final2,
    d.DO_DATE AS date_emission,
    d.DO_DEBUTPERIOD AS debut_periode,
    d.DO_FINPERIOD AS fin_periode, 
    d.DO_REF AS numero_piece_externe,
    d.DO_TIERS AS ref_societe_sage, 
    d.DO_TotalTTC - d.DO_TotalHTNet AS total_tva,
    d.DO_TotalHTNet AS total_ht,
    d.DO_TotalTTC AS total_ttc,
    d.DO_TYPE AS type_document,
    DOC.prix_revient_total,
    d.DO_TotalHT - DOC.prix_revient_total as total_marge
FROM F_DOCENTETE d
join F_COLLABORATEUR c ON d.CO_NO = c.CO_NO
INNER JOIN
(
    SELECT dl.DO_Piece
        , SUM(dl.DL_Qte * dl.DL_PrixRU) as prix_revient_total
    FROM F_DOCLIGNE dl
    cross join F_DOCENTETE de
    GROUP BY dl.DO_Piece
) AS DOC ON d.DO_Piece = DOC.DO_Piece
WHERE d.DO_PIECE LIKE 'BC%'

Not sure why you are getting the error message you are getting. It would be really helpful to see table definitions.

I have no idea why the OP has a `CROSS JOIN` to `F_DOCENTETE ` in lateral query; it's literally never referenced. It seems like it would only cause duplications. :/ — Thom A, Jul 18 '19 at 16:02
@Larnu I totally agree. Seems like something is pretty off there. Which could easily be a result of using old style joins and the join predicates being missed. To be honest I think this could be done very differently and avoid using a subquery (or apply) entirely. But with no details who really knows. — Sean Lange, Jul 18 '19 at 16:03
Sorry for the lack of answers. Here's the structure : F_DOCENTETE ↪ CO_No DO_Piece AB_No CA_NUM .... F_COLLABORATEUR ↪ CO_No F_DOCLIGNE ↪ DO_Piece DL_Qte DL_Prix_RU .... — Thomas Lefloch, Jul 19 '19 at 07:39

score 0 · Accepted Answer · answered Jul 19 '19 at 08:05

Thanks to Sean's answer, i think i managed to find the solution.

No CROSS JOIN needed, and it's working fine.

Here's the query :

SELECT 
col.CO_No as nom_commercial,
det.DO_PIECE AS ref_document,
det.AB_NO AS numero_abonnement, 
det.CA_NUM AS compte_analytique,  
det.CBMODIFICATION AS modified_on, 
det.CT_NUMPAYEUR AS ref_societe_payeur,
det.DO_CONTACT AS contact, 
det.DO_COORD01 AS nom_client_final1, 
det.DO_COORD02 AS nom_client_final2,
det.DO_DATE AS date_emission,
det.DO_DEBUTPERIOD AS debut_periode,
det.DO_FINPERIOD AS fin_periode, 
det.DO_REF AS numero_piece_externe,
det.DO_TIERS AS ref_societe_sage, 
det.DO_TotalTTC - det.DO_TotalHTNet AS total_tva,
det.DO_TotalHTNet AS total_ht,
det.DO_TotalTTC AS total_ttc,
det.DO_TYPE AS type_document,
DOC.prix_revient_total,
det.DO_TotalHTNet - DOC.prix_revient_total as total_marge
FROM F_DOCENTETE as det
join F_COLLABORATEUR col on det.Co_No = col.Co_No 
INNER JOIN
(
    SELECT lg.DO_Piece,
        SUM((lg.DL_Qte)*(lg.DL_PrixRU)) as prix_revient_total
    FROM F_DOCLIGNE as lg
    GROUP BY lg.DO_Piece
) AS DOC ON det.DO_Piece = DOC.DO_Piece
WHERE  (det.DO_PIECE LIKE 'BC%')

How to fix : The multi-part identifier "%.*ls" could not be bound

2 Answers2