Hi Baan gurus,
I need to fetch the total number of cartoon,qunatity,amount from the below listed table per iscom125.koor, per subc.name, per subc.shdt, per subc.item and per subc.ccde. each record

i need all the tables listed below in my code to fetch this data, all validations are mandatory. the code is working fine, but i need to optimize this, Can you please suggest.

thanks in advance.


select iscom125.*, iscst901.*
from iscom125, iscst901, tdltc001, issfc005, tdsls045,
sdshp910, tcmcs010
where iscom125.koor = :iscom125.koor
and iscom125.scon = :subc.name
and iscom125.ssdt = :subc.shdt
and tdsls045._index3 = {iscom125.pino}
and iscst901._index3 = {tdsls045.orno, tdsls045.pino,:subc.item}
and iscst901.inco = tcyesno.no
and tdltc001._index9 = {iscst901.clot}
and issfc005._index1 = {tdltc001.pwst}
and issfc005.ccty = :subc.ccde
and sdshp910._index1 = {iscst901.otyp, iscst901.pino}
and tcmcs010._index1 = {sdshp910.cdac}
and tcmcs010.freg = tcfreg.europe
order by iscst901._index1
selectdo
if iscst901.pino <> last.pino then
sum.carton = sum.carton + iscom125.tctn
sum.qty = sum.qty + iscom125.tqty
sum.amount = sum.amount + round(iscst901.avtr,no.dec.price,1)*(iscom125.tqty)
endif
last.pino = iscst901.pino
endselect

Never used most of these tables - to optimize use indexes where possible. Plus if you are only pulling a few fields do not use iscust901.* - include the actual fields. I recommend getting the baan optimization guide from infor.

I am not familiar with the 'is' package tables. But, the reason why your query is running slow (which I am assuming is what you mean by trying to 'optimize') is because of the order by iscst901._index1 clause - try removing it and see if it improves.

If you have to keep the order by clause, then surround your select with 'driver' table select that is like a master table that has the values which can constrain index1 in the where clause of your query.