Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2002
    Location
    Wisconsin
    Posts
    37

    Unanswered: Simple Cursor has me stumped

    Here's the cursor:

    EXEC SQL DECLARE CUR01 CURSOR FOR
    SELECT ACCT_NBR
    FROM DKLDB001.USB_ACCOUNT
    WHERE CLNT_ID = :W-CLNT-ID
    FOR FETCH ONLY
    I verified the format and content of both fields. I displayed the value in W-CLNT-ID and it matches 4 rows in the table.

    CLNT_ID CHAR(8) NOT NULL
    W-CLNT-ID PIC X(8).

    When I fetch, I get a SQLCODE of 100.

    If I remove the WHERE clause it works fine; it's a small table. I'm completly stumped, again.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Did you open the cursor?

    Make sure you put the Declare Cursor in the Working Storage Section and not in the Procedure Divsion.
    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 2002
    Location
    Wisconsin
    Posts
    37
    I'm no expert on DB2, but I thought the Cursor declaration can go anywhere after the host variables that it uses are defined; just so it's before the open. Like I said, if I remove the where clause it runs just fine, so there's nothing wrong with where it's declared or opened.

    I tried moving it to working storage just in case.... same results. I should mention this cursor is in a stored procedure driven from WLM, just in case that matters.
    Last edited by Balr14; 11-04-10 at 00:07.

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Long shot:
    - the variable W-CLNT-ID must be defined within a "begin declare" and "end declare" block .
    - the variable W-CLNT-ID must be filled before the OPEN CURSOR.

    Please check.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by Balr14 View Post
    I displayed the value in W-CLNT-ID and it matches 4 rows in the table.
    That clearly is not so, otherwise the query would return results. If, for example, you are comparing something like '1234____' and '____1234' (underscores designate spaces), they may look the same on screen but will not match when compared by a computer.

  6. #6
    Join Date
    Sep 2002
    Location
    Wisconsin
    Posts
    37
    Quote Originally Posted by dr_te_z View Post
    Long shot:
    - the variable W-CLNT-ID must be defined within a "begin declare" and "end declare" block .
    - the variable W-CLNT-ID must be filled before the OPEN CURSOR.

    Please check.
    Yes! I had the open in the wrong place. If you were closer, I'd kiss you!

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Balr14 View Post
    I'm no expert on DB2, but I thought the Cursor declaration can go anywhere after the host variables that it uses are defined; just so it's before the open. Like I said, if I remove the where clause it runs just fine, so there's nothing wrong with where it's declared or opened.

    I tried moving it to working storage just in case.... same results. I should mention this cursor is in a stored procedure driven from WLM, just in case that matters.
    It is better to put it in the Procedure Division since you will not be tempted to check the return code of the declare cursor statement (since it will not have a return code). For those who do check the return code of a Declare Cursor in COBOL, they are really checking the return code of the previous SQL statement, and that can lead to logic errors. I have seen this happen many times.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Sep 2002
    Location
    Wisconsin
    Posts
    37
    When I ran into this problem it was after 17 hours of working toward a noon deadline for delivering a "proof of concept" demo. I was dead tired and probably looked at that cursor and the open 20 times and never thought to look where I opened it. This was the last part I needed to complete my demo, so I beat my deadline by 1/2 hour.

  9. #9
    Join Date
    Nov 2010
    Posts
    2
    In murky politics of Japan, should this result WOW Gold at least two things clear: Mr. Ozawa is not the force he and his supporters held him, yet he should be able to Buy WOW Gold pull longer the strings behind the scenes. He may have helped orchestrate the party's historic victory in last September, and set in train reforms helped to WOW Power Leveling end Japan's long history of domination of one party. But personally, he is, for most voters, like a swig dirty dirty dishwater-by financial scandal, lack of Aion Gold transparency and accountability: in short, a reminder of the nature of the final fantasy gil Policy hoping they get rid of last year

Posting Permissions

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