Kader,

To answer the technical question:
Add a compound statement (BEGIN ... END) like:
WHILE ... DO
BEGIN
DECLARE cur2 CURSOR
...
END;
END WHILE;

But I am concerned: Why the nested cursors? Why can't this be done using a
single cursor using a join?
Note that you are operating with an RDBMS not a file system. All RDMBS love
joins, they hate cursors.
I even suspect you can write the whole logic using a single INSERT
statement with a nestec SELECT and w few CASE expressions and no cursor at
all.
Performance will be a magnitude better than the nested cursor approach.

Cheers
Serge

"Mati Kader Ben "
<db2udbdba-ezmlmshield-x35825006.[Email address protected] wrote on
07/29/2005 05:22:28 PM:

> Hi Ganesh,
>
> I cannot declare the second cursor just next the first one because
> the select of the second cursor uses the results of the first one.
> for that reason I need to declare the second one after the fetch of
> the first one.
>
> Kader
>
>
> -----Original Message-----
> From: ganesh
> [mailto:db2udbdba-ezmlmshield-x67434620.[Email address protected]
> Sent: July 29, 2005 5:01 PM
> To: LazyDBA Discussion
> Subject: Re: Stored procedure
>
>
>
> Hi,
> Declare your second cursor next to the first cursor,
> then write your logic.
>
>
> Thanks,
> Regards,
> Ganesh Murugesan
> 570-341-6221
>
>
>
> |---------+--------------------------------------------------------->
> | | "Mati Kader Ben " |
> | | <db2udbdba-ezmlmshield-x97657252.[Email address protected]
> | | om> |
> | | |
> | | |
> | | Friday July 29, 2005 04:14 PM |
> | | |
> |---------+--------------------------------------------------------->
>
>
>------------------------------------------------------------------------------------

> |
> |
|
> | To: "LazyDBA
> Discussion" |
> | <[Email address protected]
|
> | cc:
|
> | Subject: Stored procedure
|
>
>
>------------------------------------------------------------------------------------

> |
>
>
>
>
> 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
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>


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