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?
DECLARE
statements must precede all executable statements in a block. All variables (e.g.MAX_DATE
) must be declared before that. – mustaccio