Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Unanswered: "WHERE IN" clause in a cursor

    Hi.
    I want to use WHERE IN ('XXX', 'YYY', 'ZZZ'....) in a DB2 cursor (Cobol Programme) with different values charged in a table declared at working. I may have anywhere from 1 to about 99 unknown values.

    Tks in advance for your help!

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    For this you have to prepare host structured table (you know how) put your values in this table (h-str-table).

    Then change you statement to Where your_column in :h-str-table...

    01 ws-str_table
    05 filler x(3) value 'XXX'.
    05 filler x(3) value 'YYY'.
    05 filler x(3) value 'ZZZ'.
    ..............
    ..............
    ..............

    01 h-str-table redefines ws-str_table pic x(300).

    Lenny
    Last edited by Lenny77; 03-19-12 at 14:27.

  3. #3
    Join Date
    Mar 2012
    Posts
    4
    Lenny77
    Tks for your explanation. But i'm having a problem that i can't resolve.
    I must say that the 99 variables are unknown, that's why i'm putting an OCURRS.
    Here there's de declaration of the table:
    ...01 TABLE.
    .......05 TAB-FIELD PIC 9(03) OCCURS 10.
    ...01 TABLE2 REDEFINES TABLE PIC X(30).

    Before the open of the cursor, I put all the variables in the table.
    Then, here is the acces to my DB:

    ...SELECT A,B,C
    .....FROM xxxxxxxx
    ....WHERE MY_COL IN (:TABLE2)

    Doesn't work.
    What am I doing wrong?
    And my apologizes if I'm doing something stupid, I can't really see it

  4. #4
    Join Date
    Mar 2012
    Posts
    4
    I'm getting [SQLERR -401 The operands of an arithmetic or comparison operation are not comparable] at compile time.
    MY_COL is declared as: PIC S9(3)V USAGE COMP-3.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Using static SQL implies that the number if values between the parentheses after the "IN" must be known at bind time.

    So what you want to do is impossible, at least in the way you try to do it.

    Two potential ways out, as I see it:

    1) Declare a temporary table with one column, store the 'n' values in it, then modify the "IN" query to
    Code:
    WHERE MY_COL IN (SELECT * FROM temp_tbl)
    2) Use dynamic SQL, i.e., write the SELECT statement into an X(1000) at run time, e.g. using the STRING cobol statement, and have your cursor refer to that dynamic sql variable.
    (I could give details if you want.)

    I guess option 1 is the easier one to implement...
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Mar 2012
    Posts
    4
    Peter.Vanroose
    Tks for your help! I'm still having a problem.. when binding the pgm, I get a -204 because it doesn't recognises the table in working.
    Here's the declaration:
    ...01 TABLE.
    ......05 TAB-FIELD PIC 9(03) OCCURS 10.
    ...01 TABLE2 REDEFINES TABLE PIC X(30).

    And here's how i'm doing the access to the table:
    ...SELECT field1,field2,field3
    ...FROM xxxxxxxx
    ...WHERE MY_COL IN (SELECT * FROM TABLE2)
    Last edited by mmaggiee; 03-20-12 at 09:20.

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Thumbs down Where are YOU FROM, guy ?

    Quote Originally Posted by mmaggiee View Post
    Peter.Vanroose
    Tks for your help! I'm still having a problem.. when binding the pgm, I get a -204 because it doesn't recognises the table in working.
    Here's the declaration:
    ...01 TABLE.
    ......05 TAB-FIELD PIC 9(03) OCCURS 10.
    ...01 TABLE2 REDEFINES TABLE PIC X(30).

    And here's how i'm doing the access to the table:
    ...SELECT field1,field2,field3
    ...FROM xxxxxxxx
    ...WHERE MY_COL IN (SELECT * FROM TABLE2)
    You have to learn DB2. This is not a poetry.
    1.
    Code:
       Exec SQL 
         DECLARE GLOBAL TEMPORARY TABLE session.temp_tbl 
        (GLB_COL   Decimal(3,0) Not Null)
       End-Exec
    2.
    Code:
        exec SQL 
              insert into session.temp_tbl 
              values (123) 
        end-exec
        
        exec SQL 
              insert into session.temp_tbl 
              values (456) 
        end-exec
    Repeat insert how many times required.

    3. Finally:

    Code:
       exec SQL 
          select * from YOUR_TBL
          WHERE MY_COL IN (SELECT GLB_COL FROM session.temp_tbl )
       end-exec
    Lenny

  8. #8
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Wink Small change is required

    Quote Originally Posted by mmaggiee View Post
    Lenny77
    Tks for your explanation. But i'm having a problem that i can't resolve.
    I must say that the 99 variables are unknown, that's why i'm putting an OCURRS.
    Here there's de declaration of the table:
    ...01 TABLE.
    .......05 TAB-FIELD PIC 9(03) OCCURS 10.
    ...01 TABLE2 REDEFINES TABLE PIC X(30).

    Before the open of the cursor, I put all the variables in the table.
    Then, here is the acces to my DB:

    ...SELECT A,B,C
    .....FROM xxxxxxxx
    ....WHERE MY_COL IN (:TABLE2)

    Doesn't work.
    What am I doing wrong?
    And my apologizes if I'm doing something stupid, I can't really see it
    Small change is required for you query:

    Code:
    EXEC-SQL
            SELECT  A,B,C
              FROM  xxxxxxxx
            WHERE  Varchar(MY_COL) IN  (:TABLE2)
    END-EXEC
    Lenny

Tags for this Thread

Posting Permissions

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