Clicky

Hi,

 I have this stored procedure. But when i'm compiling it, it's throwing the following error.
 Please help.

[IBM][CLI Driver][DB2/AIX64] SQL0206N  "SESSION.REM.RETURN_DT" is not valid in the context where it is used.  LINE NUMBER=32.

 CREATE PROCEDURE CHESSIE.REM_STITCH (IN V_START_DT DATE, IN V_END_DT DATE) ------------------------------------------------------------------------ -- SQL Stored Procedure ------------------------------------------------------------------------ P1: BEGIN DECLARE V_REM_DT DATE; DECLARE V_RET_DT DATE; DECLARE V_OLD_REM_DT DATE; DECLARE V_OLD_RET_DT DATE; DECLARE V_CLIENT_ID INTEGER; DECLARE GLOBAL TEMPORARY TABLE REM(CLIENT_ID INTEGER,REMOVAL_DT DATE,RETURN_DT DATE) WITH REPLACE;  FOR CUR1 AS SELECT DISTINCT CLIENT_ID   FROM TB_REMOVAL  WHERE DELETE_SW = 'N' DO  FOR CUR2 AS  SELECT CLIENT_ID,REMOVAL_DT,RETURN_DT  FROM TB_REMOVAL  WHERE CLIENT_ID = CUR1.CLIENT_ID    AND DELETE_SW = 'N'  DO  SET V_REM_DT = CUR2.REMOVAL_DT;  SET V_RET_DT = CUR2.RETURN_DT;  SET V_CLIENT_ID = CUR2.CLIENT_ID;    INSERT INTO SESSION.REM  VALUES (V_CLIENT_ID,V_REM_DT,V_RET_DT);   IF SESSION.REM.RETURN_DT = V_REM_DT THEN  UPDATE SESSION.REM    SET RETURN_DT = V_RET_DT; END IF; END FOR; END FOR;  P2: BEGIN DECLARE CUR3 CURSOR WITH RETURN TO CALLER FOR SELECT * FROM SESSION.REM WHERE REMOVAL_DT <= V_END_DT   AND (RETURN_DT IS NULL OR RETURN_DT > V_START_DT) ; END P2; END P1                              
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26: 27: 28: 29: 30: 31: 32: 33: 34: 35: 36: 37: 38: 39: 40: 41: 42: 43: 44: 45: 

Select allOpen in new window

asked 11/30/2011 06:21

pvsbandi's gravatar image

pvsbandi ♦♦


1 Answers:
Hi pvsbandi,

session.rem is a table, and session.rem.return_dt is a column name.

You just inserted the value in the variable V_RET_DT into the table.  Check the variable instead of trying to reference the table.

 IF V_RET_DT = V_REM_DT THEN



Kent
link

answered

Kdo's gravatar image

Kdo

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×1

Asked: 11/30/2011 06:21

Seen: 359 times

Last updated: 12/01/2011 05:28

Categories