Hello, We have a problem i think you guys can help us to find its solution.


We have to select data in table regardless of company number (from multiple companies.) We have a many tables and a lot of select statements. The worst thing is that that dll in using some other dll where we want the same ( select data in table regardless of company number ). How can we do that, is it possible.

We know about: tablename._compnr = company_number

But we dont wana use it because we have many select statements. We want some other and simple way to do it (if available).

To be more specific, one of our target is to use it (above thing) in 'Print Reminider Letter' session 'tfacr3420m000'.

If someone did it with this session please provide your suggestion.


Thanks,
/Aamir

try using "switch.to.company()"

Regards,
Gaurav B

You can use the IN predicate of the WHERE clause. Example (not tested!):

select tcibd001.*
from tcibd001
where tcibd001._compnr IN (100, 200, 300)

Or

select tcibd001.*
from tcibd001
where tcibd001._compnr IN
(select ttaad100.comp
from ttaad100
where ttaad100._compnr = 0
and ttaad100.comp <> 0
and ttaad100.pacc = :curr.pacc$)

Thanks for reply guys.

v_kewl, we cannot use '' because we need data from multiple companies in one query and that query has many other sub queries. We cannot run these queries for each separately. We want data from all company at one time to process.


JaapJD, actually we don't want to use _compnr because we have many 'select' statements.

Is there any way in baan erpln that we tell baan in 'one statement' that we want data from given multiple companies instead of giving company number with every select statement.

any more suggestion???-


Thanks.

Using _compnr or switch.to.company is the only way I know how to do this inside a baan program. I mean how many queries and how many companies are you talking about? What kind of session are we talking about?

I don't think there's a way to run a singe select for multiple companies without using a lot of joins.

Can you use the union operator with multiple select statements?

OK, other example (not tested!):

select tdsls401.*
from tdsls401
where tdsls401._compnr in (100,200,300)
selectdo
select tcibd001.*
from tcibd001
where tcibd001._compnr = :tdsls401._compnr
and tcibd001.item = :tdsls401.item
as set with 1 rows
selectdo
....
endselect
endselect

If you have multiple select statements on highest level, you can put the company selection in a #define statement.