Dear Gurus,

I have to do grouping from 2 tables (cisli310 and tdsls401), here is the invoice line data, sales order line data, and report layout requirement :

Invoice Data (cisli310) :

Invoice No | Item Code | Order No. | Qty
(cisli310.idoc) | (cisli310.item) | (cisli310.orno) | (cisli310.dqua)
INV001 | AA | SO001 | 10
INV001 | BB | SO002 | 20
INV001 | CC | SO002 | 10
INV001 | BB | SO002 | 30
INV001 | BB | SO002 | 50


Sales Order Line Data (tdsls401) :

SO No. | Item Code | Cust. Order No. | Qty
(tdsls401.orno) | (tdsls401.item) | (tdsls401.corn) | (tdsls401.qidl)
SO001 | AA | CO001 | 10
SO002 | BB | CO003 | 20
SO002 | CC | CO003 | 10
SO002 | BB | CO003 | 30
SO002 | BB | CO002 | 50



Expected Invoice Layout :

Inv. No. : INV001

Item Code | Cust. Order No. | Qty
(cisli310.item) | (tdsls401.corn) | (cisli310.dqua)
-----------------------------------------------------------------------
AA | CO001 | 10
BB | CO002 | 50
BB | CO003 | 60
CC | CO003 | 10


I use this code in my program script


select cisli305.*
from cisli305
where cisli305.tran inrange {:tran.f} and {:tran.t}
and cisli305.idoc inrange {:idoc.f} and {:idoc.t}
selectdo
curr.tran = cisli305.tran
curr.idoc = cisli305.idoc
select cisli310.tran, cisli310.idoc, cisli310.item:curr.item, cisli310.pric:curr.pric, sum(cisli310.dqua):item.deli, sum(cisli310.amth(1)):item.amount, tdsls401.corn:curr.cust
from cisli310, tdsls401
where cisli310.tran = {:curr.tran}
and cisli310.idoc = {:curr.idoc}
and tdsls401.item = {:cisli310.item}
group by cisli310.tran, cisli310.idoc, cisli310.item, cisli310.pric, tdsls401.corn
selectdo
rprt_send()
endselect
endselect

but the result is "No Data Within Selection" :confused:
Really appreciate your kind help :)
Thank you.

Best regards,
Rendy

try replace:
and tdsls401.item = {:cisli310.item}
with:
and tdsls401.item = cisli310.item
In the second select query

Dear BChary,

Thanks for your reply.
I've tried your suggestion but still the result doesnt meet the requirement.
Btw, could you please elaborate the difference between {:cisli310.item} and cisli310.item ?
Thank you.

Best regards,
Rendy

:cisli310.item means it is coming externally from the query where as cisli310.item means it is coming as part of the query.

The way I would solve this is pull 401 table out of the query. Get it working with just the 305 and 310 table first. So use debug and maybe even pull the sums out if it still does not work. Once you have it working then add in the next component the sums, then once that works add in 401. That should show you were the problem is.

Hi Rendy,

You use ":" only when you have a select inside another select.

e.g.

select tdsls400.*
from tdsls400
selectdo
select tdsls401.*
from tdsls401
where tdsls401._index1 = :tdsls400.orno
selectdo
endselect
endselect

WITHOUT ":"

select tdsls400.*,tdsls401.*
from tdsls400,tdsls401
where tdsls401.orno = tdsls400.orno
selectdo
endselect

In case of your other question, can you check if you go inside the select statement if you do not use group by? I suspect there are no records matching groups.

Also, you can simply use cisli310 table.

And, I do not see any joins based on the Order number(tdsls401.orno/cisli310.orno) here. You are joining based on just the item code.

Dear Mark,

Thank you for the advice :)
The result is working fine after pulling out tdsls401 grouping order.
Is it possible to do grouping by cisli310.idoc, cisli310.item and tdsls401.corn ? :confused:


Dear Bhushan,

I need to do the grouping by cisli310.idoc, cisli310.item, and tdsls401.corn and i'm still confuse how to do it. :confused:
Currently, Everything is working fine without tdsls401.corn
Thank you :)


Best regards,
Rendy

You can still sort in the report by entering the sort sequence in the Input fields of the report.