The following gets created fine:
connect to iiradb@

drop procedure poorvi.updt_rank@

create procedure poorvi.updt_rank (in lvl int)
result sets 1
LANGUAGE SQL
begin
declare r_date date;
declare r_id varchar(30);
declare r_broker char(4);
declare r_totqty bigint;
declare r_rank int;
declare end int default 0;

declare global temporary table session.temp (
trans_date date ,
id varchar(12),
broker char(4),
tot_qty bigint,
rank int )
on commit preserve rows with replace in user_temp;

insert into session.temp
select trans_date,
id,
broker,
tot_qty,
dense_rank() over (partition by id order by (tot_qty) desc)
from
poorvi.advrank
where
aggrlvl = lvl ;
declare C1 cursor for select r.trans_date,r.id,r.broker,r.tot_qty,r.rank
from session.temp r ;
end
@

connect reset@


however when this is modified as follows:
connect to iiradb@

drop procedure poorvi.updt_rank@

create procedure poorvi.updt_rank (in lvl int)
result sets 1
LANGUAGE SQL
begin
declare r_date date;
declare r_id varchar(30);
declare r_broker char(4);
declare r_totqty bigint;
declare r_rank int;
declare end int default 0;

declare global temporary table session.temp (
trans_date date ,
id varchar(12),
broker char(4),
tot_qty bigint,
rank int )
on commit preserve rows with replace in user_temp;

declare C1 cursor for select r.trans_date,r.id,r.broker,r.tot_qty,r.rank
from session.temp r ;
DECLARE continue HANDLER FOR not found set end = 1; (lines added)

insert into session.temp
select trans_date,
id,
broker,
tot_qty,
dense_rank() over (partition by id order by (tot_qty) desc)
from
poorvi.advrank
where
aggrlvl = lvl ;
declare C1 cursor for select r.trans_date,r.id,r.broker,r.tot_qty,r.rank
from session.temp r ;
end
@

connect reset @

i get the following error:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returnedQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=20. SQLSTATE=42601


I understand it does not like the cursor declaration, however this is what i need to do: read row by row from temporary table so i use this to update rows in another table.


any ideas anybody?
-- thank you

That's because DECLARE CURSOR must be in the declaration section, and the global temp table declaration ends the declaration section.

I guess a workaround could be something like this:

The better way is like this:

DECLARE GLOBAL TEMPORARY TABLE ...

-- Then start a new compound statement to declare the cursor that references the GTT

BEGIN
DECLARE CURSOR ...

OPEN CURSOR ...
END; -- Begin

HTH

Andy