Skip to Content

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Dec 05, 2016 at 11:54 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Dec 05, 2016 at 01:31 PM

    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

    Add comment
    10|10000 characters needed characters exceeded