Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to use dynamic values in IN Clause

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Ramanan-R

New User


Joined: 21 Mar 2007
Posts: 66
Location: Chennai, Tamilnadu, India

PostPosted: Fri Jan 06, 2012 2:34 am    Post subject: How to use dynamic values in IN Clause
Reply with quote

Hello All,

Is it possible to use a host variable with dynamic values moved to it?

I tried moving values embedded within quotes and separated by commas but it doesn't seem to work.

Any help is much appreciated...

Thanks!
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jan 06, 2012 2:44 am    Post subject:
Reply with quote

Hello,

You can put any value you want into a host variable - as long as it is a compatible data type. Possibly i misunderstand the question.

Quote:
I tried moving values embedded within quotes and separated by commas but it doesn't seem to work.
Posting "it didn't work" is probably the biggest waste of forum time and space. . .

What did you code (post the code)?. What happened (abend, unexpected results, other)? We can only use what you provide to help you.
Back to top
View user's profile Send private message
Ramanan-R

New User


Joined: 21 Mar 2007
Posts: 66
Location: Chennai, Tamilnadu, India

PostPosted: Fri Jan 06, 2012 4:59 am    Post subject:
Reply with quote

Dick,

Code:
SELECT  A.FIELD1                                 
       ,A.FIELD2                                 
       ,A.FIELD3                                 
       ,B.FIELD1                                 
  FROM  TBLB B                                   
       ,TBLA A                                   
 WHERE  B.FIELD1        =  'ABC'               
   AND  B.FIELD2        =  'D888888888'       
   AND  A.FIELD1        =   B.FIELD1           
   AND  A.FIELD2        =   B.FIELD2           
   AND  A.FIELD3       IN ('AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'FFF')


The values, 'AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'FFF' are needs to be dynamically added but there will be atleast one value...

I tried declaring a host variable and concatenated these dynamically added values using COBOL String function but when fetching the cursor, it returns nothing....
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jan 06, 2012 9:01 am    Post subject:
Reply with quote

Hello,

Thanks for using the Code tag icon_smile.gif

What is the sqlcode of the problem run?

You need to post the code that actually encountered the problem. . . I suspect this is not it.

What happens when you run the code you've posted?
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1232
Location: Israel

PostPosted: Fri Jan 06, 2012 12:51 pm    Post subject:
Reply with quote

Where is this query running?
Is it from a COBOL program, by any chance?
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jan 06, 2012 3:55 pm    Post subject:
Reply with quote

if you have max 5 values you could do it this way.
If only 1 value is needed, you can move the same value to all 5 work-fields.
Code:
01 ws-invalues.
    03  ws-val1 pic x(3).
    03  ws-val2 pic x(3).
    03  ws-val3 pic x(3).
    03  ws-val4 pic x(3).
    03  ws-val5 pic x(3).
01 -ws-invaltab redefines ws-invalues.
    03 ws-val pic x(3) occurs 5.
...
WHERE A.FIELD3       IN (:ws-invalues)
...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts BI,TO=ZD Changing Values Tyler Dunbar DFSORT/ICETOOL 4 Tue Jul 25, 2017 12:12 am
No new posts REXX - Dynamic file Creation d_sarlie CLIST & REXX 7 Tue Jun 27, 2017 7:30 pm
No new posts Dynamic output file creation in cobol... smileheal COBOL Programming 7 Thu Jun 15, 2017 10:53 pm
No new posts Sorting of hex values Saurabh_mi DFSORT/ICETOOL 11 Thu May 25, 2017 3:49 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us