Hello
Sorry, I don't find a specific forum for DB2, please help me with this. The cursor don't open :-(, I don't know why, any ideas?
I want to update the colum "co_peticion" from table rem_tempo2 with the respective value from the table peti_imp.
The procedure is:
CREATE PROCEDURE act_copeticion (OUT errorCode INTEGER, OUT errorLabel CHAR(32)
)
RESULT SETS 0
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE acopeticion VARCHAR(50);
DECLARE acaseid VARCHAR(50);
DECLARE bcopetremedy VARCHAR(50);
DECLARE c1 CURSOR FOR
SELECT case_id, co_peticion
FROM rem_tempo2
order by case_id
FOR UPDATE OF co_peticion;
DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
SET errorCode = SQLCODE;
OPEN c1;
SET errorLabel = 'OPEN CURSOR';
WHILE ( SQLCODE != 100 )
DO
FETCH c1 INTO acaseid, acopeticion;
select codigopeticionremedy into bcopetremedy from peti_imp where case_id = acaseid;
update rem_tempo2 set co_peticion = bcopetremedy where CURRENT OF c1;
END WHILE ;
SET errorLabel = 'CLOSE CURSOR';
CLOSE c1;
END
The result is:
Value of output parameters
--------------------------
Parameter Name : ERRORCODE
Parameter Value : 100
Parameter Name : ERRORLABEL
Parameter Value : OPEN CURSOR
Return Status = 0
more information: the table rem_tempo2 have an index on case_id