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: 1243
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 Extend the decimal values in DIVISION Balaryan DFSORT/ICETOOL 3 Thu Oct 05, 2017 4:05 pm
No new posts Working with hex values in Filemanage... Emile Straker IBM Tools 0 Tue Sep 12, 2017 1:42 am
No new posts DB2 NULL Values display V S Amarendra Reddy DB2 3 Thu Sep 07, 2017 6:59 pm
No new posts Moving values to a variable of copybo... Vignesh Sid COBOL Programming 6 Wed Sep 06, 2017 1:04 pm
No new posts BI,TO=ZD Changing Values Tyler Dunbar DFSORT/ICETOOL 4 Tue Jul 25, 2017 12:12 am

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