Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Posts
    12

    Unanswered: db2 stored procedure involving global temporay table

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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:
    Code:
    DECLARE qry VARCHAR() DEFAULT "select r.trans_date,r.id,r.broker,r.tot_qty,r.rank
    from session.temp r ";
    
    DECLARE CURSOR c1 FOR stmt;
    
    DECLARE GLOBAL TEMPORARY TABLE...
    
    PREPARE stmt FROM qry;
    
    OPEN c1; 
    
    ...

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •