Skip to Content
author's profile photo Former Member
Former Member

query showing blank fields

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Feb 27, 2013 at 01:11 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

      Query Problem.JPG (157.6 kB)
  • Posted on Feb 27, 2013 at 01:43 PM

    Hi,

    To avoid blank field use isnull(value,0)

    Hope helpful

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.