I have a problem in Query when used with cobol and db2 as below

we are getting list of country codes which is character( 3) from file.
The country codes are seperated by commas(,).. This will vary each time. It can be 3 country codes or 24 country codes...etc

e.g IND,CHN,VEN


we have to write a query which will select student id's based on the country codes.

TABLE NAME STUDENT

COLUMNS : NAME( NAME OF STUDENT) ,STUDID( STUDENT IDENTIFICATION NUMBER),CTRY_CODE( COUNTRY CODES).


I have moved "IND,CHN,VEN" which is got from file to temporary variable. ws-parm-ctry

and using the query

select STUDID, NAME from STUDENT where CTRY_CODE in (:ws-parm-ctry).

some how this doesnt work. pls do suggest me some solution.

Will be translated into something like
This is not legal SQL. What you really wanted is:
Notice the place of the quotes '.

This should work:
It will be translated into something like

I would loop through the string byte by byte, once I found the delimiter, I would insert the value into a global temp table. Once you've exhausted thee string, I would then execute your sql as a join to the temp table

Hi Wim,

sorry for the delay reply.
Iam getting an error too many arguments. I hope posstr function is taking 'IND,CHN,VEN' as string and searching one country code. Have you tried this by executing. Can you pls execute and send me the result.

Can you send me the DDL (CREATE TABLE statements) and DML (SELECT statements) ?

Hi WIM,

I thank you so much for the help you had done to me. I couldnt do that Query with POSSTR, but was able to do with LOCATE.

I heard that POSSTR will be removed in DB2 V8.2 , It is true?

LOCATE works only for string, Is there any such Query which will work for Integers for the same condition.

I am eagerly waiting for your reply.

I once again thank you very much for helping me.


The table was a sample table I had choosen for a larger solution. I may not be able to provide the original table as per company restrictiuons. I hope you understand my difficuly.

LOCATE is a special case of POSSTR, in which you can specify the starting position from where to start looking for the search-string. It surprises me that you can't get it working with POSSTR. Can you give the failing SQL and the error?
That would mean rewriting all the existing programs that use POSSTR. Normally DB2 wants to be back compatible as much as possible. Don't believe a word of it. Doesn't DB2 v8.2 already exist about 2 years?
In this case, you'd better use the solution suggested by Brett Kaiser. His solution is more general, but a bit more complex.
If you only need strings, I'd use the POSSTR-solution.
If you need other data types too, you'd better use Brett's solution.
I'm not interested in the (confidential) data, but in the structure of the table, at least the few columns that would make it possible for somebody else to replicate your problem and find a solution. I hope you understand my difficulty.