Could not be bound

Transact-SQL
1

Hi,

I,m lost, I'm learning SQL 2008 R2 but I cannot found the error on my code, the error says : "The multi-part identifier gbkmut.faktuurnr could not be bound"

please help Me, Thanks in advance

This is my code :

SELECT gbkmut.datum, Type, gbkmut.QtyIn AS QtyIn, gbkmut.QtyOut AS QtyOut, gbkmut.AvgPrice, gbkmut.warehouse_location, gbkmut.facode, gbkmut.artcode, gbkmut.valcode, gbkmut.crdnr, gbkmut.docnumber, gbkmut.bkstnr_sub, gbkmut.periode, gbkmut.faktuurnr, gbkmut.IBTDeliveryNr, gbkmut.project, gbkmut.bkstnr, gbkmut.reknr, gbkmut.oms25, gbkmut.Note, gbkmut.Attach, gbkmut.warehouse, gbkmut.debnr, gbkmut.afk, gbkmut.paid, gbkmut.Debit, gbkmut.Credit, (CASE WHEN gbkmut.AvgPrice IS NULL THEN NULL ELSE CASE WHEN gbkmut.SalesPrice = 0 THEN NULL ELSE ((gbkmut.SalesPrice - gbkmut.AvgPrice) / gbkmut.SalesPrice * 100)END END) AS margin, gbkmut.bdr_val, gbkmut.verwerknrl, gbkmut.betaalref, gbkmut.id, gbkmut.Lotnumber FROM (
SELECT gbkmut.datum, gbkmut.periode, gbkmut.faktuurnr,
gbkmut.docnumber, gbkmut.project, gbkmut.bkstnr, gbkmut.reknr,
gbkmut.artcode, gbkmut.transtype, gbkmut.transsubtype, CASE WHEN gbkmut.type IN (191,190,151,152,150) THEN CONVERT(VARCHAR, gbkmut.type) ELSE gbkmut.transsubtype END as Type,
gbkmut.Note, gbkmut.Attach, gbkmut.res_id, gbkmut.oorsprong, gbkmut.ord_soort,
gbkmut.QtyIn, gbkmut.QtyOut, gbkmut.warehouse, gbkmut.warehouse_location, gbkmut.oms25, gbkmut.afk, gbkmut.bkstnr_sub,
gbkmut.debnr, gbkmut.crdnr , gbkmut.paid, gbkmut.Debit, gbkmut.Credit,
gbkmut.SalesPrice,
ABS(CASE WHEN (ISNULL(gbkmut.QtyIn,0.0)+ISNULL(gbkmut.QtyOut,0.0)) = 0.0 THEN NULL ELSE
CASE WHEN gbkmut.QtyIn IS NOT NULL AND gbkmut.QtyIn <> 0.0 THEN (ISNULL(gbkmut.Debit,0.0) / gbkmut.QtyIn)
ELSE CASE WHEN gbkmut.QtyOut IS NOT NULL AND gbkmut.QtyOut <> 0.0 THEN (ISNULL(gbkmut.Credit,0.0) / gbkmut.QtyOut)
ELSE NULL END END END) AS AvgPrice,
gbkmut.valcode, gbkmut.bdr_val,gbkmut.verwerknrl, gbkmut.betaalref, gbkmut.id, gbkmut.IBTDeliveryNr , gbkmut.facode, gbkmut.Lotnumber
FROM (
SELECT gbkmut.datum, gbkmut.periode, gbkmut.faktuurnr,
gbkmut.docnumber, gbkmut.project, gbkmut.bkstnr,
ISNULL(de.debcode, '') AS debnr, ISNULL(cr.crdcode, '') AS crdnr,
(CASE WHEN grtbk.omzrek = 'D' THEN (CASE WHEN (SELECT sum(g.bdr_hfl) FROM gbkmut g INNER JOIN grtbk ON grtbk.reknr = g.reknr WHERE g.transtype IN ('X','N','C','P')
AND g.faktuurnr = gbkmut.faktuurnr) = 0 THEN 1 ELSE 0 END) ELSE 0 END) AS paid,
gbkmut.reknr, gbkmut.artcode, gbkmut.transtype, gbkmut.transsubtype, gbkmut.type,
(CASE WHEN gbkmut.documentID IS NULL THEN 0 ELSE 1 END) AS Note,
(CASE WHEN gbkmut.docattachmentID IS NULL THEN 0 ELSE 1 END) AS Attach,
(CASE WHEN gbkmut.transtype = 'B' THEN NULL ELSE CASE WHEN grtbk.omzrek = 'J' AND gbkmut.aantal < 0 THEN -gbkmut.aantal ELSE
CASE WHEN grtbk.omzrek IN ('G', 'K', 'N') AND gbkmut.aantal > 0 THEN gbkmut.aantal END END END) AS QtyIn,
(CASE WHEN gbkmut.transtype = 'B' THEN NULL ELSE CASE WHEN grtbk.omzrek = 'J' AND gbkmut.aantal > 0 THEN gbkmut.aantal ELSE
CASE WHEN grtbk.omzrek IN ('G', 'K', 'N') AND gbkmut.aantal < 0 THEN -gbkmut.aantal END END END) AS QtyOut,
gbkmut.warehouse,gbkmut.warehouse_location,gbkmut.oms25, Dagbk.afk, gbkmut.bkstnr_sub,
gbkmut.res_id, gbkmut.oorsprong, ISNULL(orkrg.ord_soort, (CASE WHEN NOT gbkmut.crdnr IS NULL THEN 'B' WHEN NOT gbkmut.debnr IS NULL THEN 'V' WHEN NOT gbkmut.res_id=0 THEN 'I' ELSE ' ' END)) AS ord_soort,
(CASE WHEN grtbk.omzrek IN ('J', 'K', 'G', 'N') THEN (CASE WHEN btwtrs.exclus = 'E' THEN items.salespackageprice ELSE
(CASE WHEN gbkmut.valcode = 'EUR' THEN ROUND((items.salespackageprice/((100 + btwtrs.btwper)*0.01)),0) ELSE ROUND((items.salespackageprice/((100 + btwtrs.btwper)*0.01)),2) END)END)ELSE NULL END) AS SalesPrice,
(CASE WHEN gbkmut.transtype = 'B' THEN NULL ELSE CASE WHEN transsubtype NOT IN ('R','S')THEN CASE WHEN bdr_hfl >= 0 THEN bdr_hfl ELSE NULL END ELSE CASE WHEN bdr_hfl < 0 THEN bdr_hfl ELSE NULL END END END) AS Debit,
(CASE WHEN gbkmut.transtype = 'B' THEN NULL ELSE CASE WHEN transsubtype NOT IN ('R','S') THEN CASE WHEN bdr_hfl >= 0 THEN NULL ELSE -bdr_hfl END ELSE CASE WHEN bdr_hfl < 0 THEN NULL ELSE -bdr_hfl END END END) AS Credit, (CASE WHEN gbkmut.transsubtype = 'A' THEN 'MXN' ELSE gbkmut.valcode END ) AS valcode, (CASE WHEN gbkmut.transsubtype = 'A' AND gbkmut.valcode <> 'MXN' THEN gbkmut.bdr_hfl ELSE gbkmut.bdr_val END ) AS bdr_val
,gbkmut.verwerknrl, gbkmut.betaalref, gbkmut.ID, gbkmut.IBTDeliveryNr, gbkmut.facode, ItemNumbers.Lotnumber
FROM gbkmut
LEFT OUTER JOIN cicmpy de ON gbkmut.debnr = de.debnr AND gbkmut.debnr IS NOT NULL AND de.debnr IS NOT NULL
LEFT OUTER JOIN cicmpy cr ON gbkmut.crdnr = cr.crdnr AND gbkmut.crdnr IS NOT NULL AND cr.crdnr IS NOT NULL
LEFT OUTER JOIN dagbk ON gbkmut.dagbknr = dagbk.dagbknr AND gbkmut.dagbknr IS NOT NULL INNER JOIN grtbk ON gbkmut.reknr = grtbk.reknr
LEFT OUTER JOIN orkrg ON gbkmut.bkstnr_sub = orkrg.ordernr AND gbkmut.bkstnr_sub IS NOT NULL AND orkrg.ordernr IS NOT NULL
LEFT OUTER JOIN ItemNumbers ON gbkmut.artcode = ItemNumbers.Itemcode AND gbkmut.facode = ItemNumbers.Number AND gbkmut.facode IS NOT NULL
INNER JOIN items ON gbkmut.artcode = items.itemcode
LEFT OUTER JOIN btwtrs ON items.salesvatcode = btwtrs.btwtrans AND items.salesvatcode IS NOT NULL AND btwtrs.btwtrans IS NOT NULL
WHERE gbkmut.artcode = 'SM4-PERRINS' AND gbkmut.reknr = '110008001' AND gbkmut.aantal > 0 AND gbkmut.transtype IN ('X', 'N', 'C', 'P') AND gbkmut.remindercount <= 12 AND gbkmut.warehouse='1 '
) AS gbkmut ) AS gbkmut
ORDER BY gbkmut.datum, gbkmut.periode, gbkmut.faktuurnr, gbkmut.ID

2

Quick comment, use different aliases. you use gbkmut at least twice.

3

Hi dj55,

that's part of my problem, I am lost, between the identificator name and where I must use it.

4

Split the query into pieces that you can run separately. Then combine when you understand what the pieces do.

If you have not done it, format the code so you can read it easier. See http://www.dpriver.com/pp/sqlformat.htm as one site to get formatting.

Your select within a select within a select is very confusing and without the original tables it gets hard to follow.

5

here is my poor attempt at format

SELECT 
    y.datum, 
    Type, 
    y.QtyIn AS QtyIn, 
    y.QtyOut AS QtyOut, 
    y.AvgPrice, 
    y.warehouse_location, 
    y.facode, 
    y.artcode, 
    y.valcode, 
    y.crdnr, 
    y.docnumber, 
    y.bkstnr_sub, 
    y.periode, 
    y.faktuurnr, 
    y.IBTDeliveryNr, 
    y.project, 
    y.bkstnr, 
    y.reknr, 
    y.oms25, 
    y.Note, 
    y.Attach, 
    y.warehouse, 
    y.debnr, 
    y.afk, 
    y.paid, 
    y.Debit, 
    y.Credit, 
    (CASE 
        WHEN y.AvgPrice IS NULL THEN NULL 
        ELSE 
            CASE 
                WHEN y.SalesPrice = 0 THEN NULL 
                ELSE ((y.SalesPrice - y.AvgPrice) / y.SalesPrice * 100)
                END 
        END) AS margin, 
    y.bdr_val, y.verwerknrl, y.betaalref, y.id, y.Lotnumber 
FROM (

    SELECT x.datum, x.periode, x.faktuurnr,
        x.docnumber, x.project, x.bkstnr, x.reknr,
        x.artcode, x.transtype, x.transsubtype, 
        CASE WHEN x.type IN (191,190,151,152,150) THEN CONVERT(VARCHAR, x.type) ELSE x.transsubtype END as Type,
        x.Note, x.Attach, x.res_id, x.oorsprong, x.ord_soort,
        x.QtyIn, x.QtyOut, x.warehouse, x.warehouse_location, x.oms25, x.afk, x.bkstnr_sub,
        x.debnr, x.crdnr , x.paid, x.Debit, x.Credit,
        x.SalesPrice,
        ABS(CASE 
                WHEN (ISNULL(x.QtyIn,0.0)+ISNULL(x.QtyOut,0.0)) = 0.0 THEN NULL 
                ELSE CASE 
                        WHEN x.QtyIn IS NOT NULL AND x.QtyIn <> 0.0 THEN (ISNULL(x.Debit,0.0) / x.QtyIn)
                        ELSE 
                            CASE 
                                WHEN x.QtyOut IS NOT NULL AND x.QtyOut <> 0.0 THEN (ISNULL(x.Credit,0.0) / x.QtyOut)
                                ELSE NULL 
                            END 
                        END 
                END) AS AvgPrice,
        x.valcode, x.bdr_val,x.verwerknrl, x.betaalref, x.id, x.IBTDeliveryNr , x.facode, x.Lotnumber
    FROM (

        SELECT gbkmut.datum, gbkmut.periode, gbkmut.faktuurnr,
            gbkmut.docnumber, gbkmut.project, gbkmut.bkstnr,
            ISNULL(de.debcode, '') AS debnr, ISNULL(cr.crdcode, '') AS crdnr,
            (CASE WHEN grtbk.omzrek = 'D' THEN (CASE WHEN (
            
                SELECT sum(g.bdr_hfl) FROM gbkmut g 
                INNER JOIN grtbk ON grtbk.reknr = g.reknr 
                WHERE g.transtype IN ('X','N','C','P')
                    AND g.faktuurnr = gbkmut.faktuurnr) = 0 THEN 1 ELSE 0 END) 
               ELSE 0 END) AS paid,

            gbkmut.reknr, gbkmut.artcode, gbkmut.transtype, gbkmut.transsubtype, gbkmut.type,
            (CASE WHEN gbkmut.documentID IS NULL THEN 0 ELSE 1 END) AS Note,
            (CASE WHEN gbkmut.docattachmentID IS NULL THEN 0 ELSE 1 END) AS Attach,
            (CASE 
                WHEN gbkmut.transtype = 'B' THEN NULL 
                ELSE CASE WHEN grtbk.omzrek = 'J' AND gbkmut.aantal < 0 THEN -gbkmut.aantal 
                ELSE CASE WHEN grtbk.omzrek IN ('G', 'K', 'N') AND gbkmut.aantal > 0 THEN gbkmut.aantal END END END) AS QtyIn,
            (CASE 
                WHEN gbkmut.transtype = 'B' THEN NULL 
                ELSE CASE 
                        WHEN grtbk.omzrek = 'J' AND gbkmut.aantal > 0 THEN gbkmut.aantal 
                        ELSE CASE 
                            WHEN grtbk.omzrek IN ('G', 'K', 'N') AND gbkmut.aantal < 0 THEN -gbkmut.aantal 
                            END 
                        END 
                END) AS QtyOut,
            gbkmut.warehouse,gbkmut.warehouse_location,gbkmut.oms25, Dagbk.afk, gbkmut.bkstnr_sub,
            gbkmut.res_id, gbkmut.oorsprong, ISNULL(orkrg.ord_soort, 
                                                    (CASE 
                                                        WHEN NOT gbkmut.crdnr IS NULL THEN 'B' 
                                                        WHEN NOT gbkmut.debnr IS NULL THEN 'V' 
                                                        WHEN NOT gbkmut.res_id=0 THEN 'I' 
                                                        ELSE ' ' 
                                                        END)) AS ord_soort,
            (CASE 
                WHEN grtbk.omzrek IN ('J', 'K', 'G', 'N') THEN 
                    (CASE 
                        WHEN btwtrs.exclus = 'E' THEN items.salespackageprice 
                        ELSE (CASE 
                                WHEN gbkmut.valcode = 'EUR' THEN ROUND((items.salespackageprice/((100 + btwtrs.btwper)0.01)),0) 
                                ELSE ROUND((items.salespackageprice/((100 + btwtrs.btwper)0.01)),2) 
                                END)
                        END) 
                ELSE NULL 
                END) AS SalesPrice,
            (CASE 
                WHEN gbkmut.transtype = 'B' THEN NULL 
                ELSE CASE 
                        WHEN transsubtype NOT IN ('R','S')THEN 
                        CASE 
                            WHEN bdr_hfl >= 0 THEN bdr_hfl 
                            ELSE NULL 
                            END 
                        ELSE CASE 
                                WHEN bdr_hfl < 0 THEN bdr_hfl 
                                ELSE NULL 
                                END 
                        END 
                END) AS Debit,
            (CASE 
                WHEN gbkmut.transtype = 'B' THEN NULL 
                ELSE CASE 
                        WHEN transsubtype NOT IN ('R','S') THEN 
                        CASE 
                            WHEN bdr_hfl >= 0 THEN NULL 
                            ELSE -bdr_hfl 
                            END 
                        ELSE CASE 
                                WHEN bdr_hfl < 0 THEN NULL 
                                ELSE -bdr_hfl 
                                END 
                        END 
                END) AS Credit, 
            (CASE 
                WHEN gbkmut.transsubtype = 'A' THEN 'MXN' 
                ELSE gbkmut.valcode 
                END ) AS valcode, 
            (CASE 
                WHEN gbkmut.transsubtype = 'A' AND gbkmut.valcode <> 'MXN' THEN gbkmut.bdr_hfl 
                ELSE gbkmut.bdr_val 
                END ) AS bdr_val
            ,gbkmut.verwerknrl, gbkmut.betaalref, gbkmut.ID, gbkmut.IBTDeliveryNr, gbkmut.facode, ItemNumbers.Lotnumber
        FROM gbkmut
        LEFT OUTER JOIN cicmpy de   ON gbkmut.debnr = de.debnr               AND gbkmut.debnr IS NOT NULL           AND de.debnr IS NOT NULL
        LEFT OUTER JOIN cicmpy cr   ON gbkmut.crdnr = cr.crdnr               AND gbkmut.crdnr IS NOT NULL           AND cr.crdnr IS NOT NULL
        LEFT OUTER JOIN dagbk       ON gbkmut.dagbknr = dagbk.dagbknr        AND gbkmut.dagbknr IS NOT NULL 
        INNER JOIN grtbk            ON gbkmut.reknr = grtbk.reknr
        LEFT OUTER JOIN orkrg       ON gbkmut.bkstnr_sub = orkrg.ordernr     AND gbkmut.bkstnr_sub IS NOT NULL      AND orkrg.ordernr IS NOT NULL
        LEFT OUTER JOIN ItemNumbers ON gbkmut.artcode = ItemNumbers.Itemcode AND gbkmut.facode = ItemNumbers.Number AND gbkmut.facode IS NOT NULL
        INNER JOIN items            ON gbkmut.artcode = items.itemcode
        LEFT OUTER JOIN btwtrs      ON items.salesvatcode = btwtrs.btwtrans  AND items.salesvatcode IS NOT NULL     AND btwtrs.btwtrans IS NOT NULL
        WHERE gbkmut.artcode = 'SM4-PERRINS' 
            AND gbkmut.reknr = '110008001' 
            AND gbkmut.aantal > 0 
            AND gbkmut.transtype IN ('X', 'N', 'C', 'P') 
            AND gbkmut.remindercount <= 12 
            AND gbkmut.warehouse='1 '
        ) AS x 

    ) AS y
ORDER BY y.datum, y.periode, y.faktuurnr, y.ID;
6

Thank You so much dj55 it works, I will using the Format thing.

Kind Regards...