View previous topic :: View next topic
|
Author |
Message |
ovreddy
Active User
Joined: 06 Dec 2004 Posts: 211 Location: Keane Inc., Minneapolis USA.
|
|
|
|
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 |
|
|
ravi17s Warnings : 1 New User
Joined: 15 Aug 2003 Posts: 57
|
|
|
|
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 |
|
|
ovreddy
Active User
Joined: 06 Dec 2004 Posts: 211 Location: Keane Inc., Minneapolis USA.
|
|
|
|
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 |
|
|
ravi17s Warnings : 1 New User
Joined: 15 Aug 2003 Posts: 57
|
|
|
|
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 |
|
|
parikshit123
Active User
Joined: 01 Jul 2005 Posts: 269 Location: India
|
|
|
|
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 |
|
|
|