Help required to solve query problem

[former_member193355]

Dear All Experts,

I have a sample query belows:

    SELECT

    CASE WHEN RowNum IN (1) THEN docnum ELSE '' END AS field1,

  CASE

    WHEN RowNum = 1 THEN itemcode else 'subtotal'

  END AS ProductName

     ,case when isnull(grd,0) = '' then '' else GRD end as grd,

     CASE

    WHEN RowNum = 3 THEN  0

    ELSE qty3

  END AS qty

FROM

(

    SELECT distinct

        ITEMCODE,

        CASE

            WHEN GROUPING(docnum) = 1 and grouping(itemcode)=1 THEN 'GrandTotal'

            --WHEN GROUPING(itemcode) in (1,2) THEN 'Subtotal'

            ELSE CONVERT(VARCHAR(12), docnum, 113)

        END AS docnum,

        sum(isnull(x.qty,0)) AS qty3,

        ROW_NUMBER() OVER (PARTITION BY isnull(docnum,'') ORDER BY itemcode) AS RowNum,

        X.GRDOC AS GRD

    FROM

        (

        select distinct a.itemcode [itemcode], a.docnum [docnum], TEMP_OIGN.OIGN_DOCNUM [GRDOC], TEMP_OIGN.OIGN_ItemCode [prodid], TEMP_OIGN.oign_product as qty

         from owor a inner join wor1 b on b.docentry = a.docentry

         left join

         (

         SELECT distinct

  A.baseref AS [OWOR_DocEntry],

  A.DocEntry AS [OIGN_DocEntry],

  A.ItemCode AS [OIGN_ItemCode],

  isnull(B.DocNum,'') AS [OIGN_DOCNUM],

  C.InvntryUom AS [OITM_InventoryUom],

  a.Quantity as oign_product,

  A.LineNum AS [IGN1_SequenceNumber]

  FROM IGN1 A INNER JOIN OIGN B ON B.DocEntry=A.DocEntry

  INNER JOIN OITM C ON A.ItemCode = C.ItemCode

  WHERE A.BaseType = '202' AND A.BaseLine IS NULL

  ) TEMP_OIGN ON a.DocEntry = TEMP_OIGN.OWOR_DocEntry and a.ItemCode = TEMP_OIGN.OIGN_ItemCode

where a.Status <> 'c'

) x

  where    x.docnum between '161' and '162' and x.GRDOC <> 0

    GROUP BY

        itemcode,

        docnum

       , qty

       ,GRDOC

    WITH ROLLUP

) AS T

where t.docnum <>''

ORDER BY

    CASE WHEN itemcode IS NULL THEN 0 ELSE 1 END DESC, -- Grand Total last.

    T.docnum,

    T.itemcode

    ASC

The result of the above query is as follow:

How to remove the marked values in the column 'productname', 'grd' amd 'qty' ? I have tried hard to solve but failed. Thank you

Rgds,

Steve

[Former Member]

Hi Steve,

Try:

SELECT

    CASE WHEN RowNum IN (1) THEN docnum ELSE '' END AS field1,

  CASE

    WHEN RowNum = 1 THEN itemcode else 'subtotal'

  END AS ProductName

     ,case when isnull(grd,0) = '' then '' else GRD end as grd,

     CASE

    WHEN RowNum = 3 THEN  0

    ELSE qty3

  END AS qty

FROM

(

    SELECT distinct

        ITEMCODE,

        CASE

            WHEN GROUPING(docnum) = 1 and grouping(itemcode)=1 THEN 'GrandTotal'

            --WHEN GROUPING(itemcode) in (1,2) THEN 'Subtotal'

            ELSE CONVERT(VARCHAR(12), docnum, 113)

        END AS docnum,

        sum(isnull(x.qty,0)) AS qty3,

        ROW_NUMBER() OVER (PARTITION BY isnull(docnum,'') ORDER BY itemcode) AS RowNum,

        X.GRDOC AS GRD

    FROM

        (

        select distinct a.itemcode [itemcode], a.docnum [docnum], TEMP_OIGN.OIGN_DOCNUM [GRDOC], TEMP_OIGN.OIGN_ItemCode [prodid], TEMP_OIGN.oign_product as qty

         from owor a inner join wor1 b on b.docentry = a.docentry

         left join

         (

         SELECT distinct

  A.baseref AS [OWOR_DocEntry],

  A.DocEntry AS [OIGN_DocEntry],

  A.ItemCode AS [OIGN_ItemCode],

  isnull(B.DocNum,'') AS [OIGN_DOCNUM],

  C.InvntryUom AS [OITM_InventoryUom],

  a.Quantity as oign_product,

  A.LineNum AS [IGN1_SequenceNumber]

  FROM IGN1 A INNER JOIN OIGN B ON B.DocEntry=A.DocEntry

  INNER JOIN OITM C ON A.ItemCode = C.ItemCode

  WHERE A.BaseType = '202' AND A.BaseLine IS NULL

  ) TEMP_OIGN ON a.DocEntry = TEMP_OIGN.OWOR_DocEntry and a.ItemCode = TEMP_OIGN.OIGN_ItemCode

where a.Status <> 'c'

) x

  where    x.docnum between '161' and '162' and x.GRDOC <> 0

    GROUP BY

        itemcode,

        docnum

       , qty

       ,GRDOC

    WITH ROLLUP

    Having qty > 0

) AS T

where t.docnum <>''

ORDER BY

    CASE WHEN itemcode IS NULL THEN 0 ELSE 1 END DESC, -- Grand Total last.

    T.docnum,

    T.itemcode

    ASC

Thanks,

Gordon