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
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