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: 1224
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: 1278
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 REXX DB2: Dynamic allocation of DB2.D... BHAS CLIST & REXX 3 Mon Dec 19, 2016 8:26 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts How can we create a flat file in JAVA... rakesh.v18 Java & MQSeries 7 Fri Sep 23, 2016 10:46 pm


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