remove duplicate rows.

[VinoKR]

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

[KennedyT21]

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

[former_member184146]

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

[kothandaraman_nagarajan]

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