Skip to Content

Help required in query

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Dec 09, 2013 at 04:11 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 09, 2013 at 10:20 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 09, 2013 at 10:26 AM

    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.

    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.