I would like to ask on the 'In' operator in the use of SELECT clause.
First I know this would work:
select ...
...
and warehouse in ("CH1", "CHF", "CHE")
...

So I wonder in the following:
if foreign.flag = true then
cwar.1 = "CH1"
cwar.2 = "CHF"
cwar.3 = "CHE"
else
cwar.1 = "UN1"
cwar.2 = "UNF"
cwar.3 = "UNE"
endif

select ...
...
and warehouse in (cwar.1, cwar.2, cwar.3)
...
But like the code I wrote, it says error. How to solve it?

Hi,

in Baan this is usually coded as follows:


if foreign.flag = true then
cwar.1 = "CH1"
cwar.2 = "CHF"
cwar.3 = "CHE"
else
cwar.1 = "UN1"
cwar.2 = "UNF"
cwar.3 = "UNE"
endif

select ....
from ....
where ....
and (warehouse=:cwar.1 or warehouse=:cwar.2 or warehouse=:cwar.3)
....


but on second thought I think you just forgot to add the semicolons :rolleyes: these are needed as cwar.1 etc are bounded variables. Try this:

and warehouse in (:cwar.1, :cwar.2, :cwar.3)

Hi,

in Baan this is usually coded as follows:


if foreign.flag = true then
cwar.1 = "CH1"
cwar.2 = "CHF"
cwar.3 = "CHE"
else
cwar.1 = "UN1"
cwar.2 = "UNF"
cwar.3 = "UNE"
endif

select ....
from ....
where ....
and (warehouse=:cwar.1 or warehouse=:cwar.2 or warehouse=:cwar.3)
....


but on second thought I think you just forgot to add the semicolons :rolleyes: these are needed as cwar.1 etc are bounded variables. Try this:

and warehouse in (:cwar.1, :cwar.2, :cwar.3)


No, the point is not this. I also have tried as your said.
and warehouse in (:cwar.1, :cwar.2, :cwar.3)
But it is not the correct way.

After the in operatore you can only use constants, not variables. This is the problem.

You can try (maybe works) the wherebind option:


select ....
from ....
where ....
and warehouse in (:1, :2, :3)

wherebind(1, foreign.flag ? "CH1" : "UNI")
wherebind(2, foreign.flag ? "CHF" : "UNF")
wherebind(3, foreign.flag ? "CHE" : "UNE")
....

After the in operatore you can only use constants, not variables. This is the problem.

You can try (maybe works) the wherebind option:


select ....
from ....
where ....
and warehouse in (:1, :2, :3)

wherebind(1, foreign.flag ? "CH1" : "UNI")
wherebind(2, foreign.flag ? "CHF" : "UNF")
wherebind(3, foreign.flag ? "CHE" : "UNE")
....





Sorry, this method is not true. Error:":1" not expected.

Sorry, I havent the system to check when I posted the reply.
So, in the IN(...) operator you can only put constants, not variables. There is no way to do what you want. You have to use warehouse = ... or ... instead of the IN operator.

Sorry, I havent the system to check when I posted the reply.
So, in the IN(...) operator you can only put constants, not variables. There is no way to do what you want. You have to use warehouse = ... or ... instead of the IN operator.

It's a shame! I can only use this method to do.
Anyway, thanks for your reply.

And thanks for all replies.

If u build a string using the flag and use dynamic query , it will work for sure . In embedded query one always need to put constants for in operators .

Alternatively u can write 2 different embedded SQLs depending on the conditions (which seems more easy work) .