Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1276
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 LOW VALUES as a KEY juares castro DB2 10 Sat Apr 07, 2018 1:06 am
No new posts Query to compare 2 values of 1 column... Poha Eater DB2 13 Fri Mar 09, 2018 10:45 am
No new posts Enterprise PL/I - dynamic allocation ... prino PL/I & Assembler 0 Mon Jan 29, 2018 10:17 pm
No new posts attchmvs from rexx subroutine that re... harisukumaran CLIST & REXX 1 Fri Jan 19, 2018 5:29 am
No new posts Date in where clause - Windows Karthikeyan Subbarayan DB2 9 Wed Nov 15, 2017 9:07 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us