Cursor with CASE

Question

I have a SQL Cursor called by a program something like this

   EXEC SQL

        DECLARE STOCK_070 CURSOR FOR
        SELECT
        A.CLIENT_ID,
        C1.CUR_ASMT_SCD,

        FROM VSTOCK A

        LEFT JOIN VASSES C1
        ON  C1.CLIENT_ID = A.CLIENT_ID
                  WHERE
        B1.STKPL_RULE_NBR  = :STRSN-STKPL-RULE-NBR
        AND DATE(A.CDTTM) >= :IOB3-DATE1
        AND    DATE(A.CDTTM) <= :IOB3-DATE2

  *RESTART
        AND
        (A.CLIENT_ID  = :STOCK-CLIENT-ID
         OR
         A.CLIENT_ID  > :STOCK-CLIENT-ID)
  *RESTART

        ORDER BY
             CASE WHEN C1.CUR_ASMT_SCD = '06'
                   THEN 1
             WHEN  C1.CUR_ASMT_SCD = '04'
                   THEN 2
             WHEN  C1.CUR_ASMT_SCD = '07'
                   THEN 3
                 ,A.CLIENT_ID
                  END
         END-EXEC.
        EXEC SQL

This works without the CASE statements. But, of course, after the CASE is added the behaviour is messed up because the RESTART doesn't account for the CASEs.

To further explain - its a cursor, so its called, say, a MaxCall of 3 times (MaxCall defined in the program, not shown here). Then the cursor is closed. Then if the user requires more results, the cursor is RESTARTED. Hence why the restart code is important - it tells the Database to not start at the beginning all over again.

For example,

This dataset - (in the format A.CLIENT_ID-C1.CUR_ASMT_SCD) = (20-6,21-6,22-6,23-6,01-4,27-04,29-4).

Should display in this order:

 20,21,22,23,01,27,29

If there is a MaxCall of 3, and there is NO RESTART CODE in the SQL - it would return

 20,21,22,20,21,22,20,21,22 ... ad infinitum.
 (which explains why a Restart is needed).

With the currently shown Restart (after the CASE statements in Order by were added):

 20,21,22,23,27,29

Leaving some results out (because Client 01 is lower than 23 and is so discarded).

So, What I need is a way to alter the Restart Code to account for the added CASE Statements in the Order By Clause.

Is there a way to solve this via SQL?

Why do you use leading zeros when calculating with numbers? Integers do not have leading zeros. Why do you think that the one row gets discarded - that does not make sense to me... — MichaelTiefenbacher, Aug 22 '18 at 05:13
Why do you use `(A.CLIENT = :STOCK-CLIENT) OR (A.CLIENT > :STOCK-CLIENT)` instead of `(A.CLIENT >= :STOCK-CLIENT)`? — jmarkmurphy, Aug 22 '18 at 11:08
Also what version of DB2 are you using, and on what platform? Finally, please add code around the `RESTART` as that is apparently where the problem manifests itself. What is that `/* lots of statements */` maybe give an example. — jmarkmurphy, Aug 22 '18 at 11:21
Is it really running under Cics ???, why would you need a restart in CICS — Bruce Martin, Aug 24 '18 at 04:18
I don't know enough to answer that clearly. In my shop the Client Screen (CICS) calls a Server which in turn calls an I/O (which is above). — Ardia, Aug 24 '18 at 15:19

Douglas Korinke · Answer 1 · 2018-08-23T13:02:37.527

0

It looks to me like your missing an END on the CASE statement and perhaps using WITH HOLD on your cursor would help.

EXEC SQL

    DECLARE STOCK_070 CURSOR WITH HOLD FOR
    SELECT
    A.CLIENT_ID,
    C1.CUR_ASMT_SCD,

    FROM VSTOCK A

    LEFT JOIN VASSES C1
    ON  C1.CLIENT_ID = A.CLIENT_ID
              WHERE
    B1.STKPL_RULE_NBR  = :STRSN-STKPL-RULE-NBR
    AND DATE(A.CDTTM) >= :IOB3-DATE1
    AND    DATE(A.CDTTM) <= :IOB3-DATE2

*RESTART
    AND
    (A.CLIENT_ID  = :STOCK-CLIENT-ID
     OR
     A.CLIENT_ID  > :STOCK-CLIENT-ID)
*RESTART

    ORDER BY
         CASE WHEN C1.CUR_ASMT_SCD = '06'
               THEN 1
         WHEN  C1.CUR_ASMT_SCD = '04'
               THEN 2
         WHEN  C1.CUR_ASMT_SCD = '07'
               THEN 3
         END
             ,A.CLIENT_ID

     END-EXEC.
    EXEC SQL

edited Aug 23 '18 at 13:02

answered Aug 23 '18 at 12:10

Douglas Korinke

389
7
20

Even if so, this does not get to the heart of the question. – Ardia Aug 23 '18 at 12:52
I'm not sure how the code would have worked correctly without the end. Does the behavior persist when corrected? – Douglas Korinke Aug 23 '18 at 12:57
Yes...it was a typo...just edited it. The original code had the END...pasted it wrong. – Ardia Aug 23 '18 at 13:01
Fair enough, see my revised code on the DECLARE CURSOR line using WITH HOLD. I'm not sure which version of DB2 you are on, but the IBM documentation at https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/apsg/src/tpc/db2z_heldnonheldcursor.html seems be a good start. – Douglas Korinke Aug 23 '18 at 13:04
I read through With Hold, and Im not sure it applies. This is for a Program run in CICS - so Close cursor is applied after each MaxCall cycle...and from what I read and test - the hold is removed on Close Cursor. – Ardia Aug 23 '18 at 20:15
Are you able to store the last ran client ID in a table in order to reference after the cursor closes the first time? – Douglas Korinke Aug 24 '18 at 01:02
I could store it if there was no restart in the SQL above, but of course that creates the ad infinitum result. To store it properly I got to get the restart key to include the Order By Case data somehow. – Ardia Aug 24 '18 at 15:16
I was meaning through the use of another table to persist the value after close. You would need a stored procedure though and that may or may not be possible in your situation. – Douglas Korinke Aug 24 '18 at 16:46

Cursor with CASE

1 Answers1