title: SQL - Stored Procedure in IBM DB2
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So above works. But when I try deploying this below it fails:</p>
  
  <pre><code>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 &gt; MAX_DATE AND ANOMALY=2 ORDER BY 2, 1;
  
      OPEN AD_DATA;
  
  END P1
  </code></pre>
  
  <p>Then I get this error messages:</p>
  
  <p>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.</p>
  
  <p>What am I doing wrong?</p>