Hi ,
How to apply aggregate function on array column of a field in LN SQL queries.


Thanks,
Rajitha

Can you provide more details on table and field you are looking at? Might help someone come up with a suggestion. I mean worse comes to worse, you can put each field on the report. Then edit the layout and add the fields together. I am sure one of our LN gurus will have a better suggestion.

may be this will help...


function read.main.table()
{
#undef end()
if job.process then
....
...
...
...

select cisli310.srcp:srcp,
sum( case cisli305.stdb
when cisli.stdb.settl.invoice then cisli310.slah(1) - ((cisli310.ldai + cisli310.odai) *( case cisli310.slai
when 0 then 0.0
else cisli310.slah(1)/cisli310.slai
end ))
else cisli310.amth(1) - ((cisli310.ldai + cisli310.odai) *( case cisli310.amti
when 0 then 0.0
else cisli310.amth(1)/cisli310.amti
end ))
end ):inv.amt.hc1
from cisli305,cisli310
where cisli305._index1 inrange {:curr.comp, :ityp.f, :idoc.f}
and {:curr.comp, :ityp.t, :idoc.t}
and cisli310._index1 = {cisli305.sfcp,cisli305.tran,cisli305.idoc}
and cisli305.idat inrange :date.f and :date.t
and cisli305.doct inrange :doct.f and :doct.t
and cisli310.srcp = :i.comp
and cisli310.invt <> tcinvt.installment
group by cisli310.srcp
selectdo
endselect

Hi,

Also note, if its Easy SQL Query, AFAIK, it is not possible to apply aggregate function to an array field e.g. tfacp200.amth(1)

So, in that case, as Mark Suggested, you can go ahead and play with your report script.