Page 1 of 1

Variable IN clause

PostPosted: Tue Dec 24, 2019 10:00 am
by ravi11081992
Hi,

I got a requirement to use IN clause with different host variable values like below

If condition = true
     Move "'T', 'V', 'W'" to :ws-variable
Else
     Move "'A', 'C'" to :ws-variable
End-if


I tried the below DB2 query but it's not accepting the syntax

Select employee_id into
           :Ws-employee-id
From employee
Where
Employee_variable in :ws-variable
 

I am getting error ws-variable cannot be used like this

As the number of values in 'IN' clause is changing, we cannot hard code the value in the host-variable

Can you please help

Thanks

Re: Variable IN clause

PostPosted: Tue Dec 24, 2019 2:50 pm
by NicC
Please use the code tags to pesent yur code.

What is the EXACT message that you are getting, including the message ID. (And use the code tags to present that as well.)

Re: Variable IN clause

PostPosted: Thu Dec 26, 2019 8:22 pm
by sergeyken
ravi11081992 wrote:I got a requirement to use IN clause with different host variable values like below

If condition = true
     Move "'T', 'V', 'W'" to :ws-variable
Else
     Move "'A', 'C'" to :ws-variable
End-if


I tried the below DB2 query but it's not accepting the syntax

Select employee_id into
           :Ws-employee-id
From employee
Where
Employee_variable in :ws-variable
 

The manager who gave a requirement like this (if it is really what he required?) - he must be fired from his position in the next minute, or two.

Re: Variable IN clause

PostPosted: Sat Feb 15, 2020 5:07 am
by chaat
To accomplish this you can use Dynamic SQL and manually build an SQLDA ( SQL Descriptor Area). Do a search for SQLDA and that should point you in the correct direction. I have used this successfully in COBOL programs and it works quite nicely. It does require a bit more coding, but worthwhile if you IN list is long.

If your IN list has a small maximum number of values, for me that value was around 10, you could hard code the IN list to contain 10 variables and if you have less than 10 values, then just move you last value to the rest of the variables in the IN list.

Chuck Haatvedt