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

    Unanswered: if else and open cursors

    I am attempting to run two different select statements based on a variable.

    IF Date = (SELECT date FROM dates_table) THEN OPEN cursor1;
    ELSE
    OPEN cursor2;
    END IF;

    Can I do this? My code compiles but does not return any results Running my select satements in the cursor does return a result.

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    It may help if you post more of the process - showing the real code.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    How is the cursor declared and in which context does the statement occur? For example, in a stored procedure you may want to declare the cursor as WITH RETURN.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Jun 2012
    Posts
    4
    More detailed sql code.

    Cur2: BEGIN
    DECLARE RPT_IMSA CURSOR WITH RETURN
    FOR

    select statement;

    BEGIN
    DECLARE RPT_IMSA_2 CURSOR WITH RETURN

    IF Date = (SELECT date FROM dates_table)
    THEN OPEN RPT_IMSA;
    ELSE
    OPEN RPT_IMSA_2;
    END IF;

    End Cur2!


    FOR

    Select Statement

  5. #5
    Join Date
    Nov 2011
    Posts
    334
    Why dont you use the dynamic sql?
    something like this:
    DECLARE RPT_IMSA_2 CURSOR with return for stmt;
    IF Date = (SELECT date FROM dates_table)
    THEN PREPARE stmt FROM sql1;
    ELSE
    PREPARE stmt FROM sql12;
    END IF;

    open RPT_IMSA_2 CURSOR;

  6. #6
    Join Date
    Jun 2012
    Posts
    4
    I will give that a try. Thanks!

  7. #7
    Join Date
    Jun 2012
    Posts
    4
    should I replace the sql1 with my select statement?

    THEN PREPARE stmt FROM
    Select * from trucks;
    Else
    PREPARE stmt FROM
    Select * from cars;
    END IF;
    Open RPT_IMSA_2 cursor

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by bamajk View Post
    More detailed sql code.

    Cur2: BEGIN
    DECLARE RPT_IMSA CURSOR WITH RETURN
    FOR

    select statement;

    BEGIN
    What is this BEGIN for? It is not terminated anywhere.

    DECLARE RPT_IMSA_2 CURSOR WITH RETURN
    You're missing a select statement for this cursor.

    IF Date = (SELECT date FROM dates_table)
    This condition will only work if it is a scalar subselect, i.e. if "dates_table" is guaranteed to contain a single row only. Is that the case for you? If not, you'll get a run-time error.

    THEN OPEN RPT_IMSA;
    ELSE
    OPEN RPT_IMSA_2;
    END IF;

    End Cur2!
    Is '!' your statement terminator? If so, you want to have an "END Cur2;" here, followed by an "END!" for the stored procedure

    FOR

    Select Statement
    I have no idea what this should be.

    So if the above is really your SP code, you should fix the basic syntax problems first.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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