Jul 14, 2020 at 04:09 PM

SAP B1 Query Duplicate issue

683 Views

We have a query that is showing duplicates becuase of the T0.[Status] = R and the T0.[Status] Planned.

is there a way to nest the 2 so that we will not have duplicates ?

this is the query - we are getting one line for released and one line for Planned for each time.

SELECT Distinct T2.ItemCode, T3.ItmsGrpNam,T2.ItemName, (T4.OnHand - T4.IsCommited + T4.OnOrder) AS 'Available', T4.OnOrder AS 'ON PO', T4.MinStock, sum(case when T0.[Status] ='R' then (isnull(T1.PlannedQty,0)) else 0 End) as 'Released' ,T2.OnHand , sum(case when T0.[Status] ='P' then (isnull(T1.PlannedQty,0)) else 0 End) as 'Planned' , T2.OnHand - sum(case when T0.[Status] ='P' then (isnull(T1.PlannedQty,0)) else 0 End) AS 'Stock for Planned' ,case when (T4.OnHand - T4.IsCommited+ T4.OnOrder ) < T4.MinStock Then 'Order' else ' ' End AS 'Status' FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.[DocEntry] = T1.[DocEntry] LEFT OUTER JOIN OITM T2 ON T1.ItemCode=T2.ItemCode LEFT OUTER JOIN OITB T3 ON T2.ItmsGrpCod=T3.ItmsGrpCod LEFT OUTER JOIN OITW T4 ON T4.[ItemCode] = T1.[ItemCode] WHERE T0.[Status] <>'L' and T0.[Status]<>'C' and T1.ItemCode Like '1%' and T4.WhsCode in ('02') Group by T2.ItemCode, T3.ItmsGrpNam,T2.ItemName,T1.PlannedQty, T2.OnHand,T0.Status, T4.MinStock, (T4.OnHand - T4.IsCommited + T4.OnOrder), T4.OnOrder