SQL - Stored Procedure in IBM DB2

Question

I am trying to create a stored procedure in IBM DB2 Warehouse, using the IBM Data Studio tool (eclipse). I am having troubles using a variable I have fetched data into from a cursor. This below is the working version and returns a DATE as I wish:

CREATE OR REPLACE PROCEDURE ML_ANOMALY_EVENT_CREATOR (OUT MAX_DATE TIMESTAMP(6))
    DYNAMIC RESULT SETS 1
P1: BEGIN

    DECLARE MAX_DATE_CURSOR CURSOR FOR
        SELECT DATETIME_END FROM ML_ANOMALY_EVENTS ORDER BY DATETIME_END DESC FETCH FIRST 1 ROWS ONLY;

    OPEN MAX_DATE_CURSOR;
       FETCH FROM MAX_DATE_CURSOR INTO MAX_DATE;
    CLOSE MAX_DATE_CURSOR;

END P1

So above works. But when I try deploying this below it fails:

CREATE OR REPLACE PROCEDURE ML_ANOMALY_EVENT_CREATOR ()
    DYNAMIC RESULT SETS 1
P1: BEGIN

    DECLARE MAX_DATE_CURSOR CURSOR FOR
        SELECT DATETIME_END FROM ML_ANOMALY_EVENTS ORDER BY DATETIME_END DESC FETCH FIRST 1 ROWS ONLY;

    OPEN MAX_DATE_CURSOR;
       FETCH FROM MAX_DATE_CURSOR INTO MAX_DATE;
    CLOSE MAX_DATE_CURSOR;

    DECLARE AD_DATA CURSOR WITH RETURN FOR
        SELECT * FROM ML_AD_MV WHERE DATETIME > MAX_DATE AND ANOMALY=2 ORDER BY 2, 1;

    OPEN AD_DATA;

END P1

Then I get this error messages:

SEXERGITEST.ML_ANOMALY_EVENT_CREATOR - Deploy for debug started. Create stored procedure returns SQLCODE: -206, SQLSTATE: 42703. SEXERGITEST.ML_ANOMALY_EVENT_CREATOR: 14: "MAX_DATE" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.18.60 "MAX_DATE" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=4.18.60 SEXERGITEST.ML_ANOMALY_EVENT_CREATOR - Deploy for debug failed. SEXERGITEST.ML_ANOMALY_EVENT_CREATOR - Roll back completed successfully.

What am I doing wrong?

You have no variable MAX_DATE declared. (Not needed, use the first SELECT as a condition to the second SELECT.) — jarlh
Why are you using a cursor for a singleton select (max_date_cursor in the second sproc shown in your question)? Apart from the undefined host-variable to contain the max date, you should reconsider the code. — mao
All DECLARE statements must precede all executable statements in a block. All variables (e.g. MAX_DATE) must be declared before that. — mustaccio

Rob Wilson Rob Wilson · Accepted Answer · 2018-03-19T11:44:36

As the commentors have pointed out the MAX_DATE variable is undeclared in the second procedure. It is declared as an OUT variable in the first procedure, so that's why that one works.

You could do:

CREATE OR REPLACE PROCEDURE ML_ANOMALY_EVENT_CREATOR ()
    DYNAMIC RESULT SETS 1
P1: BEGIN

    DECLARE AD_DATA CURSOR WITH RETURN FOR
        SELECT * FROM ML_AD_MV WHERE DATETIME > (SELECT MAX(DATETIME_END) FROM ML_ANOMALY_EVENTS) AND ANOMALY=2 ORDER BY 2, 1;

    OPEN AD_DATA;

END P1

Or if you preferred the previous logic you can declare the MAX_DATE variable before you declare the cursor with a line like:

DECLARE MAX_DATE TIMESTAMP(6);

SQL - Stored Procedure in IBM DB2

1 Answers