Skip to Content
0
Dec 14, 2013 at 10:56 AM

Help required to solve query problem

28 Views

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

Attachments

repor_1.png (8.7 kB)