on 02-12-2015 6:01 AM
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...
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
108 | |
12 | |
11 | |
6 | |
5 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.