IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Help regarding IN operator


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Relational operator compare date rang... CLIST & REXX 14
No new posts ICETOOL "SUBSET" operator q... DFSORT/ICETOOL 2
No new posts tws opc 5.4 how to change operator te... IBM Tools 5
No new posts using Parse on some other operator fo... DFSORT/ICETOOL 6
No new posts Rexx not equals operator CLIST & REXX 4
Search our Forums:

Back to Top