Error: Multi-part Identifier cannot be bound

[former_member698071]

Hi all,

I'm new to SQL and I managed to create a Sales Order vs AR Invoice query. However, when I try to include the Items Group Code into the query, it tells me that the multi-part identifier "T6.ItmsGrpCod' could not be bound. This is the code I used that works.

SELECT T0.[DocDate], T0.[DocNum] as 'Sales Order No.', T0.[CardName], T9.[SlpName] AS 'Sales Employee', T0.[U_Class], T1.[ItemCode], T1.[Dscription], T1.[Quantity] as 'SO Qty', T1.[OpenQty] as 'SO Open Qty', T0.DocStatus as 'SO Status', T0.[CANCELED] AS 'Canceled', T3.[Quantity] as 'Delivered Qty', T3.[OpenQty] as 'Delivered Open Qty', T4.[Quantity] as 'Invoice Qty'

FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] left join DLN1 T2 on T2.baseentry=T1.docentry and T2.itemcode=T1.itemcode left join inv1 T3 on T3.baseentry=T2.docentry and T3.itemcode=T2.itemcode left join inv1 T4 on T4.baseentry=T1.docentry and T4.itemcode=T1.itemcode left outer join OSLP T9 on T9.SlpCode = T1.SlpCode

[lenastodal]

Thank you for visiting SAP Community to get answers to your questions. I am here to help you to get the most out of it.

First of all, I recommend that you familiarize yourself with https://community.sap.com/resources/questions-and-answers (if you haven't done so already), as it provides tips for preparing questions that draw responses from our members.
For example you:
- can outline what steps you took to find answers (and why they weren't helpful)
- can share screenshots of what you've seen/done
- can use a descriptive subject line

Please also make sure you're using all appropriate tags, so the right experts can find your question. Overall, the more details you provide, the more likely it is that members will be able to assist you. Should you wish, you can revise your question by selecting Actions, then Edit (although once someone answers your question, you'll lose the ability to edit the question - but if that happens, you can leave more details in a comment).

Finally, if you're hoping to connect with readers, please consider adding a picture to your profile. Here's how you do it: https://www.youtube.com/watch?v=F5JdUbyjfMA&list=PLpQebylHrdh5s3gwy-h6RtymfDpoz3vDS. By personalizing your profile with a photo of you, you encourage readers to respond.

Best,
Lena (SAP Community Moderator)

[MD1]

try this ,it is working

SELECT
T0."DocDate",
T0."DocNum" as "Sales Order No.",
T0."CardName",
T9."SlpName" AS "Sales Employee",
----T0."U_Class",
T1."ItemCode",
T1."Dscription",
T1."Quantity" as "SO Qty",
T1."OpenQty" as "SO Open Qty",
T0."DocStatus" as "SO Status",
T0."CANCELED" AS "Canceled",
T3."Quantity" as "Delivered Qty",
T3."OpenQty" as "Delivered Open Qty",
T3."Quantity" as "Invoice Qty",
T6."ItmsGrpCod" as "Item Group"

FROM ORDR T0
INNER JOIN RDR1 T1 ON T0."DocEntry" = T1."DocEntry"
INNER JOIN OITM T5 ON T5."ItemCode" = T1."ItemCode"
left join DLN1 T2 on T2."BaseEntry"=T1."DocEntry" and T2."ItemCode"=T1."ItemCode" and T2."BaseLine" = T1."LineNum" AND t2."BaseType" = '17'
left join inv1 T3 on T3."BaseEntry"=T2."DocEntry" and T3."ItemCode"=T2."ItemCode" and T3."BaseLine" = t2."LineNum"
left Join OITB T6 on T6."ItmsGrpCod" = T5."ItmsGrpCod"
left outer join OSLP T9 on T9."SlpCode" = T1."SlpCode"

[kothandaraman_nagarajan]

Hi,

The above query is OK, if only one line item in sales order, DO and AR invoice. If more than one line item, then we have to add line number to get correct result.

Try this,

SELECT T0.[DocDate], T0.[DocNum] as 'Sales Order No.', T0.[CardName], T9.[SlpName] AS 'Sales Employee', T0.[U_Class], T1.[ItemCode], T1.[Dscription], T1.[Quantity] as 'SO Qty', T1.[OpenQty] as 'SO Open Qty', T0.DocStatus as 'SO Status', T0.[CANCELED] AS 'Canceled', T3.[Quantity] as 'Delivered Qty', T3.[OpenQty] as 'Delivered Open Qty', T4.[Quantity] as 'Invoice Qty', T6.[ItmsGrpCod] as 'Item Group'

FROM

ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITM T5 ON T5.ItemCode = T1.ItemCode

left join DLN1 T2 on T2.baseentry=T1.docentry and T2.itemcode=T1.itemcode and T2.Baseline = T1.lineNum

left join inv1 T3 on T3.baseentry=T2.docentry and T3.itemcode=T2.itemcode and T3.Baseline = t2.LineNum

left join inv1 T4 on T4.baseentry=T1.docentry and T4.itemcode=T1.itemcode and T4.Baseline = t1.LineNum

left Join OITB T6 on T6.[ItmsGrpCod] = T5.[ItmsGrpCod] left outer join OSLP T9 on T9.SlpCode = T1.SlpCode

Regards,

Nagarajan

[former_member698071]

Thank you!

[former_member698071]

Hi,

I attempted your query and it does not show me an accurate invoice qty. Any idea how to get that fixed?