Hi gurus,

I'm getting the following errors:
QL0104N An unexpected token "<cursor declaration>" was found following "".

after the DECLARE mCursor2 CURSOR WITH RETURN FOR in the below procedure.
I need to declare the second cursor (mCursor2) to use the values of the first one (mCursor1).

Any help will be appreciated.

Regards,


Kader


----
CREATE PROCEDURE UPDATE_ATTRIBUTE ( )
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN

DECLARE cf_id INTEGER;
DECLARE field_str VARCHAR(40);
DECLARE type_id INTEGER;
DECLARE emp_id INTEGER;
DECLARE custom_str VARCHAR(255);
DECLARE custom_date DATE;
DECLARE custom_int INTEGER;

DECLARE mCursor1 CURSOR WITH RETURN FOR
SELECT cfid, field, typeid
FROM CUSTOMFIELD
WHERE USED = 1 ;

OPEN mCursor1;
FETCH mCursor1 INTO cf_id, field_str, type_id;
WHILE type_id <> 0 DO
DECLARE mCursor2 CURSOR WITH RETURN FOR
SELECT empid, field_str
FROM EMPLOYEE_CUSTOMFIELD;
OPEN mCursor2;
IF type_id = 1 THEN
FETCH mCursor2 INTO emp_id, custom_str;
ELSEIF type_id = 2 THEN
FETCH mCursor2 INTO emp_id, custom_date;
ELSE
FETCH mCursor2 INTO emp_id, custom_int;
END IF;
WHILE SQLCODE <> 100 DO
IF type_id = 1 THEN
INSERT INTO EMPLOYEE_ATTRIBUTE (attid, empid, ondate, atext) VALUES (cf_id, emp_id, TO_DATE('2000-01-01', 'yyyy-mm-dd'),custom_str);
ELSEIF type_id = 2 THEN
INSERT INTO EMPLOYEE_ATTRIBUTE (attid, empid, ondate, adate) VALUES (cf_id, emp_id, TO_DATE('2000-01-01', 'yyyy-mm-dd'), custom_date);
ELSEIF type_id = 3 THEN
INSERT INTO EMPLOYEE_ATTRIBUTE (attid, empid, ondate, along) VALUES (cf_id, emp_id, TO_DATE('2000-01-01', 'yyyy-mm-dd'), custom_int);
ELSEIF type_id = 4 THEN
INSERT INTO EMPLOYEE_ATTRIBUTE (attid, empid, ondate, atime) VALUES (cf_id, emp_id, TO_DATE('2000-01-01', 'yyyy-mm-dd'), custom_int);
ELSEIF type_id = 5 THEN
INSERT INTO EMPLOYEE_ATTRIBUTE (attid, empid, ondate, aboolean) VALUES (cf_id, emp_id, TO_DATE('2000-01-01', 'yyyy-mm-dd'), custom_int);
ELSEIF type_id = 6 THEN
INSERT INTO EMPLOYEE_ATTRIBUTE (attid, empid, ondate, aminute) VALUES (cf_id, emp_id, TO_DATE('2000-01-01', 'yyyy-mm-dd'), custom_int);
END IF;
END WHILE;
CLOSE mCursor2;
END WHILE;
CLOSE mCursor;

END P1

DB2 & UDB email list listserv db2-l LazyDBA home page