query showing blank fields

[Former Member]

Hi

here i have tried one but this shows blank fields in between what to do with it? and some cases it is showing entries those are 2*original entry e.g. original entry is 100 insted of 100 it shows 200

SELECT DISTINCT T0.[ItemCode], T0.[Dscription],

CASE WHEN (T0.[JrnlMemo] like 'Receipt from Production%%' and T2.Series IN (803,822)) THEN SUM(T0.[InQty]) END as 'Prod Qty',

CASE WHEN  (T0.[JrnlMemo] like 'Goods Receipt PO%%' and T3.series = '785') THEN SUM(T0.[InQty]) END as 'Purchase Qty',

CASE WHEN (T0.[JrnlMemo] like 'Goods Issue%%' and T1.[Series] = '751' and  T1.[U_57F4SERS] in (828,829)) THEN SUM(T0.[OutQty]) END as ' Fettling Issue Qty',

CASE WHEN (T0.[JrnlMemo] like 'Goods Receipt%%' and T2.[Series] = '806' and  T2.[U_BPNAME] IS NOT NULL) THEN SUM(T0.[InQty]) END as ' Fettling Receipt Qty',

CASE WHEN (T0.[JrnlMemo] like 'Goods Issue%%' and T1.[Series] = '751' and  T1.[U_57F4SERS] in (746,747,748,749)) THEN SUM(T0.[OutQty]) END as 'Machining Issue Qty'

FROM OINM T0 LEFT OUTER JOIN OIGE T1 ON T1.Docnum = T0.BASE_REF

LEFT OUTER JOIN OIGN T2 ON T2.Docnum = T0.BASE_REF

LEFT OUTER JOIN OPDN T3 ON T3.Docnum = T0.BASE_REF

WHERE T0.[DocDate] BETWEEN [%0] AND [%1] and T0.[ItemCode]  like 'SF%%'

GROUP BY T0.[ItemCode], T0.[Dscription],T0.[JrnlMemo],T1.[Series],T1.[U_57F4SERS],T2.[Series],T2.[U_BPNAME],T3.series

Thanks and regards

avadhut

[kothandaraman_nagarajan]

Hi,

To avoid blank field use isnull(value,0)

Hope helpful

[former_member275826]

Hi,

Plz change your join:

SELECT DISTINCT T0.[ItemCode], T0.[Dscription],

CASE WHEN (T0.[JrnlMemo] like 'Receipt from Production%%' and T2.Series IN (803,822)) THEN SUM(T0.[InQty]) END as 'Prod Qty',

CASE WHEN  (T0.[JrnlMemo] like 'Goods Receipt PO%%' and T3.series = '785') THEN SUM(T0.[InQty]) END as 'Purchase Qty',

CASE WHEN (T0.[JrnlMemo] like 'Goods Issue%%' and T1.[Series] = '751' and  T1.[U_57F4SERS] in (828,829)) THEN SUM(T0.[OutQty]) END as ' Fettling Issue Qty',

CASE WHEN (T0.[JrnlMemo] like 'Goods Receipt%%' and T2.[Series] = '806' and  T2.[U_BPNAME] IS NOT NULL) THEN SUM(T0.[InQty]) END as ' Fettling Receipt Qty',

CASE WHEN (T0.[JrnlMemo] like 'Goods Issue%%' and T1.[Series] = '751' and  T1.[U_57F4SERS] in (746,747,748,749)) THEN SUM(T0.[OutQty]) END as 'Machining Issue Qty'

FROM OINM T0 LEFT OUTER JOIN OIGE T1 ON T1.Docnum = T0.BASE_REF AND APPLOBJ=60

LEFT OUTER JOIN OIGN T2 ON T2.Docnum = T0.BASE_REF AND APPLOBJ=59

LEFT OUTER JOIN OPDN T3 ON T3.Docnum = T0.BASE_REF AND APPLOBJ=20

WHERE T0.[DocDate] BETWEEN [%0] AND [%1] and T0.[ItemCode]  like 'SF%%'

GROUP BY T0.[ItemCode], T0.[Dscription],T0.[JrnlMemo],T1.[Series],T1.[U_57F4SERS],T2.[Series],T2.[U_BPNAME],T3.series

[Former Member]

Hello,

Thanks for the reply it solves the problem for double value but insted of APPLOBJ we need to use TRASNTYPE. But problem regarding blank fields continues here i am attaching screen shot with this thread so it will become clear.

Thanks

avadhur