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?

[jitin_chawla]

Hi,

Check this below:

If does not return what is expected, then let us know and we can further check the expected result

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 
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 Join OITB T6 on T6.[ItmsGrpCod] = T5.[ItmsGrpCod]
left outer join OSLP T9 on T9.SlpCode = T1.SlpCode

regards,

Jitin

[former_member698071]

Hello,

Thank you for the help. However, when I attempted the query, I get duplicates of orders and also noticed that under T3 for 'Delivery Qty' and 'Delivered Open Qty' I get the same quantity. For example, on a sales order there is 100 cases of xyz, delivery is 100 cases, and the a/r invoice should be 100 as well. I noticed that Delivery in the select was still as T3 when AP was T3 as well. So I changed Delivery to T2 and I changed the query to include the BaseLine and LineNum like kothandaraman.nagarajan did:

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

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 = T3.LineNum

left Join OITB T6 on T6.[ItmsGrpCod] = T5.[ItmsGrpCod]

left outer join OSLP T9 on T9.SlpCode = T1.SlpCode

And the quantities are now accurate. However, for the sales orders that do not have a Delivery, it does not show an AR qty. Can this be fixed or would I have to do the query with only the Sales and the A/R?

Thanks.