I tried googling and couldn't find a good answer, so I am posting my question here. I want to use WHERE IN ('XXX', 'YYY', 'ZZZ'....) in a DB2 cursor. My problem is I don't know how many literals I would have to put in my WHERE IN clause. I may have anywhere from 1 to about 60.

I could either code a Dynamic SQL or I guess, code about 60 host variables and just use only the ones I require. Is there any other option other than the above to code a static SQL and still not code for 60 host variables...?

Environment : DB2 v8 on z/OS

Thanks.

Use a sub-select. If necessary the literals could be stored in a global temporary table.

Thanks Marcus, But I guess I am not getting it. I tried the subselect as follows:

<code>
SELECT COL2, COL3 FROM TABLE1 WHERE COL1 IN (
SELECT :WH-VAR1 FROM SYSIBM.SYSDUMMY1
)
</code>

followed by a "MOVE 'XXX, YYY' TO WH-VAR1" and it doesn't blow up, but gives me 'no rows found'. If I move just XXX or YYY, then it works. If you get a chance, I would really appreciate if you could point out my error please. Thanks for your time.

How did you get the list of values ('XXX', 'YYY', 'ZZZ'....)?

Sometimes, you can write a single, straight-forward SQL statement instead of getting a list of values then pass the list to cursor.

Storze wrote rational opinion in this thread(for another process).
HOW TO PROCESS iN FOR LOOP?

The values XXX, YYY or ZZZ come from a possible list of about 60 values selected by the end user on a front end screen (It's a HTML screen with a combo multiselect list box). So they may select one or they may select many. My app would need to find the matching records from the DB2 table

So, you have about 60 fields.
Then I thought that IN (:var1, :var2, ..., var60) must be easy and natural.
It is not necessary to combine these 60 variables into one variable(:WH-VAR1).

Like I mentioned in my original post, I am aware of coding 60 host variables or coding a dynamic SQL. I wanted to know if there's a better way of doing it and I am trying to see if I can pin Marcus's suggestion (of using a subselect) down. Thanks.

ek,
Marcus was suggesting something like:
select col from tablewhere somecol in (select somecol from my_temp_table)

and in your program you would have inserted 1 to 60 rows that contain the values you want to look for.

Dave

Thanks Dave, I see it now.