Sir,
I want to generate itemwise total sale from tdsls045 & tdsls901.
while i am writing line:

select tdsls045.cuno,item , sum(tdsls045.amnt)
from tdsls045
where tdsls045._comnr = :comp.str | comp.string contains above 100 nos of BaaN company
and tdsls045.invd between :date.f and :date.t
group by tdsls045.cuno,tdsls045.item
selectdo
rprt_send()
endselect

it is working fine.
But Now i want to pick up sale amount from tdsls901.amta instead of tdsls045.amnt . Please suggest the right way to do it .I tried it by selecting tdsls045, tdsls901 with reference to cmbb & cmba and removing group by clause from my select statement , It is taking more time .
Please suggest the right way how can I get this without loosing performance.

Thanks in advance
ss

There can many ways to achieve this like SQL query , program script etc.

Use totals in Baan report with tdsls045.item as sort field. In the program equate tdsls045._index1 to tdsls901._index1 .

If this SQL works well with 100 comapnies ,the above suggestion s also should work faster . But have never experience with 100 companies.

Good luck.

Hi,

Does tdsls901 have Cuno and item as its fields? If so, you can join the two tables and hence not lose any performance.

Maxime.

Sir,

I already tried with totals in report layout. , But while I was using with group by cuno , item & sum(tdsls045.amnt) , it is working fast. But Now I want use tdsls901.amta instead of tdsls045.amnt , I can not use group by caluse & sum(tdsls901.amta) .
Can I use group by tdsls045.cuno ,item & sum(tdsls901.amta) , cuno & item fields are not in tdsls901. Please send some sql example how I can write this select statements.

Thanks

If the totals are made in report , u can eliminate the group by etc .

select tdsls045.cuno,item , sum(tdsls901.amta)
from tdsls045,tdsls901
where tdsls045._comnr = :comp.str | comp.string contains above 100 nos of BaaN company
and tdsls045.invd between :date.f and :date.t
and tdsls045._index1 = tdsls901._index1
and tdsls045._compnr = tdsls901._compnr
group by tdsls045.cuno,tdsls045.item
selectdo
rprt_send()
endselect

Sir,
while I tried this I do not get any error during compilation , But I get the following error during runnig the Session.

1 : process 34 - No dd for ' tdsls901-01' found ( error 0)
2 : Process 34 -Fatal error : Error 215 ( Illegal state ) on SELECT
3 : Process 34 -Fatal error : Can no Continue in tdcus0445m210()

Regards

This means the company number specified is -1 which does not exist. The company numbers in comp.str may not be specified properly.

Also move up the condition for compnr ie write it as

and tdsls045._compnr = tdsls901._compnr
and tdsls045._index1 = tdsls901._index1

and NOT as

and tdsls045._index1 = tdsls901._index1
and tdsls045._compnr = tdsls901._compnr

Good luck.

Sir
I can not write like that..
tdsls045._compnr =:comp.str
tdsls045._compnr =:tdsls901._compnr

I am writting this way & getting error 215, because
comp.str ="121,105,221............"

select tdsls045.cuno,sum (tdsls901.amta):inv.amnt,tiitm001.citg ,tccom010.cbrn
from tdsls045,tdsls901,tccom010,tiitm001
where tdsls045._compnr=:comp.str
and tdsls901._compnr=:tdsls045._compnr
and tccom010._compnr=:tdsls045._compnr
and tiitm001._compnr=:tdsls045._compnr
and tdsls045.item refers to tiitm001.item
and tdsls045.cuno refers to tccom010.cuno
and tdsls045.invd between :invd.f and :invd.t
and tdsls045.ttyp between :ttyp.f and :ttyp.t
and tdsls045.invn between :invn.f and :invn.t
and tdsls045._index1 =tdsls901._index1
group by tccom010.cbrn,tdsls045.cuno,tiitm001.citg
selectdo
rprt_send()
mess(" %s ",0,tdsls045.cuno)
endselect

PLease check this statement if it work properly with 100 no's of BaaN company of tell me the right way how can I improve this.

Regards

No wonder u get this error . If u c my SQL query, there is no colon ( : ) before compnr specification . When u specify colon , it means external variable which has default value -1 and hence the error.

Sir,
when I reomves the (:) from compnr , it through an error during compilation .
( Illegal usage of companynr specification. )

Regards

Post ur code again.

Sir,
Thanks a lot for spending ur time on my querry.
I am writting as follows.

comp.str = "121,105,221"
select tdsls045.cuno,tdsls045.item,sum (tdsls901.amta),tiitm001.citg,tccom010.cbrn
from tdsls045,tdsls901,tccom010,tiitm001
where tdsls045._compnr =:comp.str
and tdsls045.invd between :invd.f and :invd.t
and tdsls045.ttyp between :ttyp.f and :ttyp.t
and tdsls045.invn between :invn.f and :invn.t
and tdsls045.item refers to tiitm001.item
and tdsls045.cuno refers to tccom010.cuno
and tdsls901._compnr = tdsls045._compnr
and tccom010._compnr = tdsls045._compnr
and tiitm001._compnr = tdsls045._compnr
and tdsls901._index1 = tdsls045._index1
group by tccom010.cbrn,tdsls045.cuno,tiitm001.citg,tdsls045.item
selectdo
rprt_send()
mess(" %s ",0,tdsls045.item)
endselect
}

I c compiler does not allow this for valid reasons.However you can accomplish
the same results with some modifications.

1. create single sql on sls045 with comp_str string. Do not use group by. Either select * or select tdsls045._compnr .
2. Do the totals in reports using sort fields.
3. In select for tdsls901 (and other tables) use the tdsls045._compnr as
an external variable and bind the same with a colon to respective table compnr.

We have done lot of such consolidation across companies with this approach and its
working.