Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Montreal
    Posts
    2

    Lightbulb Unanswered: Declare cursor with IN function in the where clause

    I have been looking over don't find answer yet !!

    I need to declare a cursor wit an IN function in the where clause
    I do I declare my Cobol variable ....

    Ex:

    var-1 pic x(80).


    exec sql declare cursor for
    select empno
    from emptable
    where empname in (:var-1)

    is this ok ???
    I do I string all different values : value1, value2, value3 ....
    or other way ??

    please help !!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Your post is not totally clear to me. As written the cursor looks fine. But I don't know what you are trying to do.

    Do you mean?

    declare cursor for
    select empno
    from emptable
    where empname in (:var-1, :var2, :var3)

    But if you try to put several multiple values all inside of :var1, it will not work the way you want.
    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
    Feb 2004
    Location
    Montreal
    Posts
    2
    OK but If I have a non determine number of value in the IN clause ??

    Is that a limitation

    empname in (:var1, :var2, :var3 .....


    thanks for your answer

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    This issue has been discussed before. There seems to be some limit to the number of variables in an IN, but I don't know what it is, and you never indicated the details needed about your environment which are requested in MUST READ BEFORE POSTING. Even before the limit is reached, there may be performance degradation if the list is very long.

    If you have static SQL, you have to code all the host variables ahead of time. If you use dynamic SQL, you can build the statement on the fly with the desired number of variables in the where clause.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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