Help required in query

[former_member193355]

Dear Experts,

I have a query sample belows:

WITH receipt AS (

        SELECT distinct owor.ItemCode, oign.DocNum, ign1.BaseRef, SUM(ign1.LineTotal) AS total_receipt,

        ROW_NUMBER() OVER (partition by owor.docentry

ORDER BY owor.docentry) as row,owor.docentry

    FROM ign1 left join OWOR on owor.DocNum=ign1.BaseRef and ign1.ItemCode=owor.ItemCode

        inner join OIGN on oign.DocEntry=ign1.DocEntry

        GROUP BY owor.ItemCode, ign1.baseref, oign.DocNum, owor.DocEntry, oign.DocDate

     ),

     issued AS (

        SELECT owor.DocEntry,

        ROW_NUMBER() OVER (partition by owor.docentry

ORDER BY owor.docentry ) as iss_row,

          OIGe.DocNum, ige1.BaseRef, sum(Isnull(ige1.linetotal,0)) as total_issue

        FROM ige1 inner join OWOR on owor.DocEntry= ige1.BaseEntry and IGE1.BaseType = '202'

        inner join WOR1 on wor1.DocEntry = owor.DocEntry inner join OIGE on oige.DocEntry= ige1.DocEntry

                group by ige1.BaseRef, oige.DocNum,oige.docentry,owor.DocEntry

     )

select

TBL2.DocNum,TBL2.[FG-Code],TBL2.[Issued Num],Isnull(TBL2.[Issued Amt],0) [Issued Amt],TBL2.[Receipt Num],TBL2.[Receipt Amt]

  from (

Select (CASE rnum when 1 then ItemCode  end) [FG-Code],

(CASE rnum when 1 then Isnull(DocNum,0)  end) [DocNum],

(CASE ISrnum when 1 then Isnull(Issued_No,0)  end) [Issued Num],

(CASE ISrnum when 1 then Isnull(issuedamount,0)  end) [Issued Amt],

(CASE RSrnum when 1 then Isnull(Receipt_No,0)  end) [Receipt Num],

(CASE RSrnum when 1 then Isnull(receiptamount,0)  end) [Receipt Amt],

(forshort) [forshort]

from (  

SELECT owor.docentry,ROW_NUMBER() OVER (partition by owor.docentry order by owor.docentry) [rnum] ,

owor.docentry [forshort],

ROW_NUMBER() OVER (partition by issued.DocNum order by issued.DocNum) [ISrnum],

ROW_NUMBER() OVER (partition by receipt.DocNum order by receipt.DocNum) [RSrnum],

owor.ItemCode, owor.DocNum, issued.DocNum as Issued_No,

       max(Isnull(issued.total_issue,0)) AS issuedamount,

       receipt.DocNum as Receipt_No,

       max(receipt.total_receipt) AS receiptamount

FROM wor1 inner join owor on owor.DocEntry = wor1.docentry

inner join OITM on oitm.itemcode = owor.ItemCode

left outer join receipt

on receipt.BaseRef = owor.docnum

left outer join issued

on isnull(issued.DocEntry,0) = isnull(owor.DocEntry,0)

GROUP BY owor.docentry,owor.ItemCode, owor.DocNum, issued.DocNum, receipt.DocNum,receipt.row ,issued.iss_row  ) TBL1 ) TBL2

where tbl2.docnum ='[%1]'

group by

TBL2.DocNum,TBL2.[FG-Code],TBL2.[Issued Amt],TBL2.[Issued Num],TBL2.[Receipt Num],TBL2.[Receipt Amt]

having isnull(TBL2.[Receipt Amt],0) - isnull(TBL2.[Issued Amt],0) <> 0

order by

Max(TBL2.forshort) , TBL2.DocNum desc

I have put a parameter : where tbl2.docnum ='[%1]'

but when executing in query generator, it gives error message

Please help me where to add the parameter docnum in the query. Thanks

Rgds,

Steve

[former_member193355 · Accepted solution]

Dear All,

Thanks for your help.

The result of both queries are only one row. Meanwhile we need 5 rows.

Please give a help to correct the query

Rgds,

Steve

[former_member193355 · Accepted solution]

Hi All,

I have solved the problem

Steve

[ganeshram_v]

Hi Steve,

Try this.

Declare @docnum as int

set @docnum = (select max(a.docnum) as docnum from owor a where docnum >= [%0])

begin

WITH receipt AS (

        SELECT distinct owor.ItemCode, oign.DocNum, ign1.BaseRef, SUM(ign1.LineTotal) AS total_receipt,

        ROW_NUMBER() OVER (partition by owor.docentry

ORDER BY owor.docentry) as row,owor.docentry

    FROM ign1 left join OWOR on owor.DocNum=ign1.BaseRef and ign1.ItemCode=owor.ItemCode

        inner join OIGN on oign.DocEntry=ign1.DocEntry

        GROUP BY owor.ItemCode, ign1.baseref, oign.DocNum, owor.DocEntry, oign.DocDate

     ),

     issued AS (

        SELECT owor.DocEntry,

        ROW_NUMBER() OVER (partition by owor.docentry

ORDER BY owor.docentry ) as iss_row,

          OIGe.DocNum, ige1.BaseRef, sum(Isnull(ige1.linetotal,0)) as total_issue

        FROM ige1 inner join OWOR on owor.DocEntry= ige1.BaseEntry and IGE1.BaseType = '202'

        inner join WOR1 on wor1.DocEntry = owor.DocEntry inner join OIGE on oige.DocEntry= ige1.DocEntry

                group by ige1.BaseRef, oige.DocNum,oige.docentry,owor.DocEntry

     )

select

TBL2.DocNum,TBL2.[FG-Code],TBL2.[Issued Num],Isnull(TBL2.[Issued Amt],0) [Issued Amt],TBL2.[Receipt Num],TBL2.[Receipt Amt]

  from (

Select (CASE rnum when 1 then ItemCode  end) [FG-Code],

(CASE rnum when 1 then Isnull(DocNum,0)  end) [DocNum],

(CASE ISrnum when 1 then Isnull(Issued_No,0)  end) [Issued Num],

(CASE ISrnum when 1 then Isnull(issuedamount,0)  end) [Issued Amt],

(CASE RSrnum when 1 then Isnull(Receipt_No,0)  end) [Receipt Num],

(CASE RSrnum when 1 then Isnull(receiptamount,0)  end) [Receipt Amt],

(forshort) [forshort]

from ( 

SELECT owor.docentry,ROW_NUMBER() OVER (partition by owor.docentry order by owor.docentry) [rnum] ,

owor.docentry [forshort],

ROW_NUMBER() OVER (partition by issued.DocNum order by issued.DocNum) [ISrnum],

ROW_NUMBER() OVER (partition by receipt.DocNum order by receipt.DocNum) [RSrnum],

owor.ItemCode, owor.DocNum, issued.DocNum as Issued_No,

       max(Isnull(issued.total_issue,0)) AS issuedamount,

       receipt.DocNum as Receipt_No,

       max(receipt.total_receipt) AS receiptamount

FROM wor1 inner join owor on owor.DocEntry = wor1.docentry

inner join OITM on oitm.itemcode = owor.ItemCode

left outer join receipt

on receipt.BaseRef = owor.docnum

left outer join issued

on isnull(issued.DocEntry,0) = isnull(owor.DocEntry,0)

GROUP BY owor.docentry,owor.ItemCode, owor.DocNum, issued.DocNum, receipt.DocNum,receipt.row ,issued.iss_row  ) TBL1 ) TBL2

where tbl2.docnum =@docnum

group by

TBL2.DocNum,TBL2.[FG-Code],TBL2.[Issued Amt],TBL2.[Issued Num],TBL2.[Receipt Num],TBL2.[Receipt Amt]

having isnull(TBL2.[Receipt Amt],0) - isnull(TBL2.[Issued Amt],0) <> 0

order by

Max(TBL2.forshort) , TBL2.DocNum desc

end

Regards,

Ganesh.

[Former Member]

Hi,

Try the Below solution...

Declare @DocNum varchar(20)

     set @DocNum=(Select W.DOcNum [DocNum] from OWOR W where W.DocNum=[%0])

     Begin

WITH receipt AS (

        SELECT distinct owor.ItemCode, oign.DocNum, ign1.BaseRef, SUM(ign1.LineTotal) AS total_receipt,

        ROW_NUMBER() OVER (partition by owor.docentry

ORDER BY owor.docentry) as row,owor.docentry

    FROM ign1 left join OWOR on owor.DocNum=ign1.BaseRef and ign1.ItemCode=owor.ItemCode

        inner join OIGN on oign.DocEntry=ign1.DocEntry

        GROUP BY owor.ItemCode, ign1.baseref, oign.DocNum, owor.DocEntry, oign.DocDate

     ),

     issued AS (

        SELECT owor.DocEntry,

        ROW_NUMBER() OVER (partition by owor.docentry

ORDER BY owor.docentry ) as iss_row,

          OIGe.DocNum, ige1.BaseRef, sum(Isnull(ige1.linetotal,0)) as total_issue

        FROM ige1 inner join OWOR on owor.DocEntry= ige1.BaseEntry and IGE1.BaseType = '202'

        inner join WOR1 on wor1.DocEntry = owor.DocEntry inner join OIGE on oige.DocEntry= ige1.DocEntry

                group by ige1.BaseRef, oige.DocNum,oige.docentry,owor.DocEntry

     )

select

TBL2.DocNum,TBL2.[FG-Code],TBL2.[Issued Num],Isnull(TBL2.[Issued Amt],0) [Issued Amt],TBL2.[Receipt Num],TBL2.[Receipt Amt]

  from (

Select (CASE rnum when 1 then ItemCode  end) [FG-Code],

(CASE rnum when 1 then Isnull(DocNum,0)  end) [DocNum],

(CASE ISrnum when 1 then Isnull(Issued_No,0)  end) [Issued Num],

(CASE ISrnum when 1 then Isnull(issuedamount,0)  end) [Issued Amt],

(CASE RSrnum when 1 then Isnull(Receipt_No,0)  end) [Receipt Num],

(CASE RSrnum when 1 then Isnull(receiptamount,0)  end) [Receipt Amt],

(forshort) [forshort]

from ( 

SELECT owor.docentry,ROW_NUMBER() OVER (partition by owor.docentry order by owor.docentry) [rnum] ,

owor.docentry [forshort],

ROW_NUMBER() OVER (partition by issued.DocNum order by issued.DocNum) [ISrnum],

ROW_NUMBER() OVER (partition by receipt.DocNum order by receipt.DocNum) [RSrnum],

owor.ItemCode, owor.DocNum, issued.DocNum as Issued_No,

       max(Isnull(issued.total_issue,0)) AS issuedamount,

       receipt.DocNum as Receipt_No,

       max(receipt.total_receipt) AS receiptamount

FROM wor1 inner join owor on owor.DocEntry = wor1.docentry

inner join OITM on oitm.itemcode = owor.ItemCode

left outer join receipt

on receipt.BaseRef = owor.docnum

left outer join issued

on isnull(issued.DocEntry,0) = isnull(owor.DocEntry,0)

GROUP BY owor.docentry,owor.ItemCode, owor.DocNum, issued.DocNum, receipt.DocNum,receipt.row ,issued.iss_row  ) TBL1 ) TBL2

where tbl2.docnum =@DocNum

group by

TBL2.DocNum,TBL2.[FG-Code],TBL2.[Issued Amt],TBL2.[Issued Num],TBL2.[Receipt Num],TBL2.[Receipt Amt]

having isnull(TBL2.[Receipt Amt],0) - isnull(TBL2.[Issued Amt],0) <> 0

order by

Max(TBL2.forshort) , TBL2.DocNum desc

end

Regards,

Sudhir B.