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
 

 

Help regarding IN operator

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

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Wed Jun 28, 2006 12:35 pm    Post subject: Help regarding IN operator
Reply with quote

Hi All,

I need help regarding IN operator in DB2. I have a COBOL table with some 10 values in it. I want to use this table as a parameter to IN operator.

I have a COBOL table like this
01 BB00.
05 BB00-Table OCCURS 10 TIMES.
10 ZIPCODE PIC 9(4).
I have 10 values in ZIPCODE(1),ZIPCODE(2),......

Now I want to write a query like...

SELECT * FROM EMP WHERE ZIP_CODE IN (:ZIPCODE);

It has to substitute all 10 ZIP codes from the table and fetch the rows if ZIPCODE matches to any one of them.

It is an urgent requirement.

Please help on this...

Thanks in advance,
Reddy.
Back to top
View user's profile Send private message

ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Thu Jun 29, 2006 2:51 pm    Post subject: Re: Help regarding IN operator
Reply with quote

The format of IN list is IN(:zip1,:zip2,:zip3.....)

The best way u can do this is by reading the table and moving the values of ZIPCODE(1) ,ZIPCODE(2) ....into an hostvariable delimited by comma
and giving the host variable in the SELECT statement.

Above can be acheived by STRING command in cobol.

Thanks,
Ravi.
Back to top
View user's profile Send private message
ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Wed Jul 05, 2006 2:51 pm    Post subject:
Reply with quote

Hi Ravi,

I don't think it will work out. Because a single COBOL variable of X(n) will be treated as a single value passed to DB2. It will check for 'zip1,zip2,zip3,....' so ',' becomes part of the variable value and query will not fetch any rows.

Query will look like this.... hope you understood....

SELECT * FROM EMP WHERE ZIP_CODE IN ('zip1,zip2,zip3,zip4,......');

Bye,
Reddy.
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Wed Jul 05, 2006 4:44 pm    Post subject:
Reply with quote

Thanks Reddy!

But is it not possible to move this value into an host varible( " 'zip1',zip'2','zip3' " )

and subtituiting in the query!

Regard,
Ravi
Back to top
View user's profile Send private message
parikshit123

Active User


Joined: 01 Jul 2005
Posts: 269
Location: India

PostPosted: Thu Jul 06, 2006 11:29 am    Post subject:
Reply with quote

Hi Reddy,

Its really not possible in STATIC SQL.

You must spacify the query with all the occurance of your cobol variables ..


Thanks,
Parikshit
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 ICETOOL "SUBSET" operator q... David Sde DFSORT/ICETOOL 2 Tue Jul 28, 2015 7:53 pm
No new posts tws opc 5.4 how to change operator te... italo_pm IBM Tools 5 Wed Apr 29, 2015 11:22 am
No new posts using Parse on some other operator fo... guptae DFSORT/ICETOOL 6 Thu Dec 11, 2014 11:11 am
No new posts Rexx not equals operator harisukumaran CLIST & REXX 5 Sat Sep 20, 2014 2:57 am
No new posts SQL CODE -132. Issue with LIKE operator GaganGarg DB2 6 Wed Jan 30, 2013 3:22 pm


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