Variable IN clause



IBM's flagship relational database management system

Variable IN clause

Postby ravi11081992 » Tue Dec 24, 2019 10:00 am

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
ravi11081992
 
Posts: 19
Joined: Mon Apr 30, 2018 5:47 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Variable IN clause

Postby NicC » Tue Dec 24, 2019 2:50 pm

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.)
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: Variable IN clause

Postby sergeyken » Thu Dec 26, 2019 8:22 pm

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.
Javas and Pythons come and go, but JCL and SORT stay forever.
User avatar
sergeyken
 
Posts: 408
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 6 times
Been thanked: 40 times

Re: Variable IN clause

Postby chaat » Sat Feb 15, 2020 5:07 am

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
chaat
 
Posts: 27
Joined: Sun Aug 16, 2009 11:07 pm
Location: St. Cloud, Minnesota
Has thanked: 0 time
Been thanked: 1 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post