Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    44

    Unanswered: "WHERE IN" clause in a DB2 Cursor

    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.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ek1975
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2003
    Posts
    44
    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.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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).
    http://www.dbforums.com/db2/1640664-...cess-loop.html

  5. #5
    Join Date
    Sep 2003
    Posts
    44
    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

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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).
    Last edited by tonkuma; 04-07-09 at 10:55.

  7. #7
    Join Date
    Sep 2003
    Posts
    44
    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.

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  9. #9
    Join Date
    Sep 2003
    Posts
    44
    Thanks Dave, I see it now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •