Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Duplicate Item Numbers from query

When I rune the following query, I get multiple item numbers even when I use the 'DISTINCT" clause. The query is here:

SELECT T1.[ItemCode], T1.[ItemName], T2.[FirmName], T1.[CardCode], T1.[SuppCatNum], T4.[ItmsGrpNam], T1.[U_U_CMG_PROGRAM], T1.[U_U_CMG_CATEG], T1.[U_CMG_STATUS], T1.[U_CMG_STORES], T1.[U_CMG_COLOR], T3.[OnHand], T1.[MinLevel], T1.[MinOrdrQty], SUM(T0.[OrderedQty]) AS Ord_Qty, SUM(T0.[PackQty]) AS Pack_Qty , SUM(T0.[Quantity]) AS Qty_Sold

FROM DLN1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OMRC T2 ON T1.FirmCode = T2.FirmCode INNER JOIN OITW T3 ON T1.ItemCode = T3.ItemCode INNER JOIN OITB T4 ON T1.ItmsGrpCod = T4.ItmsGrpCod

WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1]

GROUP BY T1.[ItemCode], T1.[ItemName], T2.[FirmName], T1.[CardCode], T1.[SuppCatNum], T4.[ItmsGrpNam], T1.[U_U_CMG_PROGRAM], T1.[U_U_CMG_CATEG], T1.[U_CMG_STATUS], T1.[U_CMG_STORES], T1.[U_CMG_COLOR], T3.[OnHand], T1.[MinLevel], T1.[MinOrdrQty], T0.[OrderedQty], T0.[PackQty], T0.[Quantity]

How can I get my query to return unique values? Thanks for your help.

Former Member
Former Member replied

Hi Nate,

Gordon is correct, try to simplify your select statement.

The first thing I would try is remove the reference to the OITW table. As a general rule, by adding in the OITW table, you will get a row for each instance of Item and Warehouse, 10 WH means the Item will appear 10 times. Your query is slightly different as you are using the On Hand column, if you are using DISTINCT, you will get one row for each distinct instance of Item and On Hand quantity, so if you have 50 items On Hand in your main WH, 25 in a second WH, 25 in a third WH and the remaining 7 WH have 0, you will get 3 rows returned.

Item 1 - 50 On Hand

Item 1 u2013 25 On Hand

Item 1 u2013 0 On Hand

Regards,

Adrian

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question