Hi All,

I am wondering what is the most efficient way to do a multi-company select on the same table. For example I have sales orders in company 100 & 200 and I want to select all the orders for the same customer (which is the same in 100 & 200) and add some other conditions.

I tried to play with ._compnr, but could not do it without a sub-query.

Any advises?

~ D7

Hi,

You can do like this:-

select ttaad100.*
from ttaad100
where ttaad100._compnr = 0
and ttaad100.comp inrange {:comp.f} and {:comp.t}
selectdo
db.set.to.default(ttdsls400)
select tdsls400.*
from tdsls400
where tdsls400._compnr = :ttaad100.comp
and tdsls400.ofbp = {Your B.P}
selectdo
endselect
endselect

Second way:-

You can just pass the company number to functions.

Thanks for your advise!
Hi,

You can do like this:-

select ttaad100.*
from ttaad100
where ttaad100._compnr = 0
and ttaad100.comp inrange {:comp.f} and {:comp.t}
selectdo
db.set.to.default(ttdsls400)
select tdsls400.*
from tdsls400
where tdsls400._compnr = :ttaad100.comp
and tdsls400.ofbp = {Your B.P}
selectdo
endselect
endselect
In range will have to go to other none relevant companies. I tried to replaced it with "IN" and it worked! But this is still a kind of a "sub query".. Is this the most efficient way?

Second way:-

You can just pass the company number to functions.
Can you elaborate more?

Hi,

Just use Call By Value concept.

Say I have a function to calculate sold quantity.

function calc.quan(domain ttaad.comp comp)
{
db.set.to.default(ttdsls401)
select tdsls401.*
from tdsls401
where tdsls401._compnr = :comp
selectdo
........
endselect
}

Now, just pass your company numbers to the function.

calc.quan(501)
calc.quan(502)

....


Else, you can also try to use switch.to.company().

There are various ways to achieve this, but you will need to choose it based on the kind of inputs you have.

I go for the first approach as maximum of our reports are based on a set of companies so I simply use "inrange" and nothing else.