Skip to Content

remove duplicate rows.

Dear SAP & SQL Experts,

When running this query

SELECT distinct T4.[Code] as 'Raw material Name', T2.ItemName, SUM(T4.[Quantity]) as 'BOMQTY', SUM((T1.[OpenQty]*T4.[Quantity])) as 'TOTALQTY'

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITT T3 ON T3.[Code] = T1.[ItemCode]

INNER JOIN ITT1 T4 ON T3.[Code] = T4.[Father]

INNER JOIN OITM T2 ON T2.[ItemCode] = T4.[Code]

WHERE T1.[LineStatus] ='o'

GROUP BY T4.[Code], T2.ItemName

The result was as expected and i have created a sample of it and attached below:

Now I need to get the instock of this item from a specific warehouse.

So, I've used the below query

SELECT distinct T4.[Code] as 'Raw material Name', T2.ItemName, SUM(T4.[Quantity]) as 'BOMQTY', SUM((T1.[OpenQty]*T4.[Quantity])) as 'TOTALQTY', t5.OnHand

FROM ORDR T0

INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

INNER JOIN OITT T3 ON T3.[Code] = T1.[ItemCode]

INNER JOIN ITT1 T4 ON T3.[Code] = T4.[Father]

INNER JOIN OITM T2 ON T2.[ItemCode] = T4.[Code]

Inner join oitw t5 on t5.Itemcode= T2.[ItemCode]

WHERE T1.[LineStatus] ='o'

GROUP BY T4.[Code], T2.ItemName, t5.OnHand

However, all of the row values are repeated and the result of this query seems like duplicated value.

I don't know what to do with this. To avoid the duplication and repeated values, I've used the distinct, even the result doesn't change.

I need ur help experts...

RESULT1.PNG (10.1 kB)
RESULT2.PNG (15.6 kB)
Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Feb 12, 2015 at 07:32 AM

    Hi,

    Try this query:

    SELECT distinct T4.[Code] as 'Raw material Name', T2.ItemName, SUM(T4.[Quantity]) as 'BOMQTY', SUM((T1.[OpenQty]*T4.[Quantity])) as 'TOTALQTY', t2.OnHand

    FROM ORDR T0

    INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

    INNER JOIN OITT T3 ON T3.[Code] = T1.[ItemCode]

    INNER JOIN ITT1 T4 ON T3.[Code] = T4.[Father]

    INNER JOIN OITM T2 ON T2.[ItemCode] = T4.[Code]

    Inner join oitw t5 on t5.Itemcode= T2.[ItemCode]

    WHERE T1.[LineStatus] ='o'

    GROUP BY T4.[Code], T2.ItemName, t2.OnHand

    Thanks & Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 12, 2015 at 07:24 PM

    Hi Vinoth

    Try the below query

    SELECT distinct T4.[Code] as 'Raw material Name', T2.ItemName, SUM(T4.[Quantity]) as 'BOMQTY',

    SUM((T1.[OpenQty]*T4.[Quantity])) as 'TOTALQTY', t5.OnHand

    FROM ORDR T0

    INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

    left JOIN OITT T3 ON T3.[Code] = T1.[ItemCode]

    inner JOIN ITT1 T4 ON T3.[Code] = T4.[Father]

    inner JOIN OITM T2 ON T2.[ItemCode] = T4.[Code]

    inner join oitw t5 on t5.Itemcode= T2.[ItemCode]

    WHERE T1.[LineStatus] ='o'

    GROUP BY T4.[Code], T2.ItemName, t5.OnHand

    order by t4.Code

    --Manish

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 13, 2015 at 03:11 PM

    Hi Vinoth Raj..

    If you need for specify warehouse then try this query

    SELECT distinct T4.[Code] as 'Raw material Name', T2.ItemName, SUM(T4.[Quantity]) as 'BOMQTY', SUM((T1.[OpenQty]*T4.[Quantity])) as 'TOTALQTY', SUM(t5.OnHand)

    FROM ORDR T0

    INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry]

    INNER JOIN OITT T3 ON T3.[Code] = T1.[ItemCode]

    INNER JOIN ITT1 T4 ON T3.[Code] = T4.[Father]

    INNER JOIN OITM T2 ON T2.[ItemCode] = T4.[Code]

    Inner join oitw t5 on t5.Itemcode= T2.[ItemCode]

    WHERE T1.[LineStatus] ='o' and t5.whscode='[%0]'

    GROUP BY T4.[Code], T2.ItemName



    Regards

    Kennedy

    Add comment
    10|10000 characters needed characters exceeded