Hi,

I've a problem using _compr in a sql query (ttadv3580m000) in ERP/LN. In BaanIV i used
... where tfgld106._compr = "100,110,120" and ...
, but in ERP/LN i get error messages.

With
... where tfgld106._compnr = "110,130" and ...
i get: SQLState 42I89: Invalid company number expression (error 302) (main() in object ottadv3180parse)

With
... where tfgld106._compnr in (110,130) and ...
i get: SQLState 42I82: _compnr IN predicate is only allowed in parse mode 'ansi' (error 302) (main() in object ottadv3180parse)

With
... where (tfgld106._compnr = 110 or tfgld106._compnr = 130) and ...
i get: SQLState 42I30: Compnr specification in OR condition (error 302) (main() in object ottadv3180parse)

Did the usage of _compnr in SQL Queries change?

I also tried company_nr, but then i get this error: Unknown column 'tfgld106.company_nr'

How to create a multicompany sql query in ERP/LN?

With kindly regards,

Douwe.

Hi Douwe,

the _compnr specifies the actual company number of a table. You can use this only with one integer value.

I've never used company_nr. Sorry!

Best regards
michael

What you written in your example should work in Baan 4 and 5.
I can’t comment for LN as I don't have experience.

Could you please share complete where condition?

The complete SQL is


select
tccom710.trdt,
tccom710.orno,
tccom710.ccde,
tccom710.fcom,
tccom710.bpid,
tccom710.isup.c,
tccom710.ccty,
tccom710.casi,
tccom710.cfrw,
tccom710.wght,
tccom710.qana,
tccom710.ccur,
tccom710.gdam,
tccom710.stam,
tccom710.rate,
tccom710.ratf,
tccom710.ttyp.c,
tccom710.ninv.c,
tfgld106.fyer,
tfgld106.fprd,
tfgld106.ocmp,
tfgld106.otyp,
tfgld106.odoc
from
tccom710,
tfgld106
where
tfgld106._compnr = 110 and
tfgld106.otyp = tccom710.ttyp.c and
tfgld106.odoc = tccom710.ninv.c and
tccom710.corg >= tccom710.corg.f and
tccom710.corg <= tccom710.corg.t and
tccom710.kotr >= tccom710.kotr.f and
tccom710.kotr <= tccom710.kotr.t and
tfgld106.olin = 1 and
tfgld106.osrl = 0 and
tfgld106.osrn = 0 and
tfgld106.fyer >= tfgld106.fyer.f and
tfgld106.fyer <= tfgld106.fyer.t and
tfgld106.fprd >= tfgld106.fprd.f and
tfgld106.fprd <= tfgld106.fprd.t


Instead of only company 110 I would like to query 15 companies.

With kindly regards,

Douwe.

Since _compnr is not really a database field of the table itself, it is only possible to use the '=' condition for it in the where clause.

In order to do what you need, you will have to place your select inside a 'for-loop' or some loop that 'supplies' one company number at a time.

You could also use a union statement...