Skip to Content
0
Aug 04, 2020 at 02:05 AM

SQL Query

100 Views

Hi, please refer to my attachment. Can someone teach me on how to remove those null data? I don't want that null value appear in the row. here is my code :

SELECT T1.[ItemCode], T1.[Dscription] as ItemName, min(case when t6.Code like 'SP OEM%' then t5.Quantity end)as SalesProjectionQty, min(case when t6.Code like 'OEM%' then t5.Quantity end)as ForecastQty, sum(T1.[Quantity]) as TotalQty, sum(T1.[LineTotal]) as LineTotal FROM OINV T0 inner JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] inner JOIN OCRD T2 ON T0.CardCode = T2.CardCode inner JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode inner JOIN OITM T4 on T4.ItemCode = T1.ItemCode inner join fct1 t5 on t5.ItemCode=t1.ItemCode inner join ofct t6 on t6.AbsID=t5.AbsID WHERE T0.[DocDate] >= '20200701' And T0.[DocDate] <= '20200731' and U_XFSet='N' and month(t5.date)=month(T0.DocDate) and year(t5.date)=year(T0.DocDate) group by T1.[ItemCode], T1.[Dscription] UNION SELECT T1.[ItemCode], T1.[Dscription], min(case when t6.Code like 'SP%' then t5.Quantity end)as SalesProjectionQty, min(case when t6.Code like 'OEM%' then t5.Quantity end)as ForecastQty, sum(T1.[Quantity]/2)*-1 as 'TotalQty', sum(T1.[LineTotal]/2)*-1 as 'LineTotal' FROM ORIN T0 inner JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry] inner JOIN OCRD T2 ON T0.CardCode = T2.CardCode inner JOIN OCRG T3 ON T2.GroupCode = T3.GroupCode inner JOIN OITM T4 on T4.ItemCode = T1.ItemCode inner join fct1 t5 on t5.ItemCode=t1.ItemCode inner join ofct t6 on t6.AbsID=t5.AbsID WHERE T0.[DocDate] >= '20200701' And T0.[DocDate] <= '20200731' and U_XFSet='N' and month(t5.date)=month(T0.DocDate) and year(t5.date)=year(T0.DocDate) group by T1.[ItemCode], T1.[Dscription]

Attachments

query.png (52.7 kB)