Query - Products taken by customer by date range WITH commodity code

[former_member239716]

Hi guys - I am trying to add to a query I already have working well. What I am adding is, the commodity code to go along with the original query.

Adding the T3.[IsCommCode] is fine but when i try to add T4.[Code] so that I can actually show the commodity code as appose to the SAP internal ID for it, the report goes pear-shaped.

I think the one thing I am struggling with here, is the joins and I can't seem to figure it.

This is as far as I have gotten with the query and I know that it doesn't work - any suggestions would be very, VERY greatfully received:

SELECT T2.[ItemCode], T2.[ItemName], T3.[ISCommCode], T4.[Code], SUM (T1.[Quantity])

FROM [dbo].[ORDR] T0, [dbo].[ODCI] T4 , [dbo].[ITM10] T3
INNER JOIN [dbo].[RDR1] T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN [dbo].[OITM] T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN [dbo].[ITM10] T3 ON T3.ItemCode = T2.ItemCode
INNER JOIN [dbo].[ITM10] T3 ON T3.[ISCommCode] = T4.[AbsEntry]

WHERE T0.[CardCode] = [%0] AND T0.[DocDate] BETWEEN [%1] AND [%2] AND T1.[ItemCode] NOT Like '%%Carriage%%' AND T1.[ItemCode] NOT Like '%%SPOOKY%%' AND T1.[ItemCode] NOT Like '%%XMAS%%'

GROUP BY
T2.[ItemCode], T2.[ItemName], T3.[ISCommCode]

Regards

Roy

[Johan_H · Accepted solution]

Hi Roy,

Your suspicions are correct, the FROM clause is in trouble.

Both the ODCI and ITM10 tables have been added to the FROM clause multiple times, and have not been joined correctly, or even at all. Look at the FROM clause and you will see that they first come after a simple comma, and then later you join ODCI for a second and third time, and the ITM10 table to the ODCI table that itself was not joined. In other words, the FROM clause is a mess.

Now I don not have the ODCI and ITM10 tables in my database, so I can only make some educated guesses. Deducing from your query, I assume that ODCI is joined over ITM10. The query generator may give you the necessary fields ready, but you would have to add the OITM table before you add the ITM10 and then the ODCI table (in your query above, it is the other way around).

Anyhoo, please give this a try:

SELECT T2.[ItemCode]
     , T2.[ItemName]
     , T3.[ISCommCode]
     , T4.[Code]
     , SUM (T1.[Quantity]) AS Quantity

FROM [dbo].[ORDR] T0
     INNER JOIN [dbo].[RDR1] T1 ON T0.DocEntry = T1.DocEntry 
     INNER JOIN [dbo].[OITM] T2 ON T1.ItemCode = T2.ItemCode 
     INNER JOIN [dbo].[ITM10] T3 ON T2.ItemCode = T3.ItemCode
     INNER JOIN [dbo].[ODCI] T4 ON T3.[ISCommCode] = T4.[AbsEntry]

WHERE T0.[CardCode] = [%0] 
  AND T0.[DocDate] BETWEEN [%1] AND [%2] 
  AND T1.[ItemCode] NOT Like '%%Carriage%%' 
  AND T1.[ItemCode] NOT Like '%%SPOOKY%%' 
  AND T1.[ItemCode] NOT Like '%%XMAS%%'

GROUP BY T2.[ItemCode]
       , T2.[ItemName]
       , T3.[ISCommCode]
       , T4.[Code]

Regards,

Johan

[former_member239716]

Johan - you sir, are a legend. Thank you SO much for your help on the last two queries, it is appreciated more than you will know.

I will study where I have made my mistakes (tbh I was using the Wizard to show how the joins would be made and then make some adjustments, but I have clearly misunderstood the process) and learn from it.

Wamrest regards

Roy

[Johan_H]

Roy, you are on the right path to learning this stuff. Please do not hesitate to ask for help here.

Regards,

Johan