Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: Cursor Declaration

    Just discovered something which I hope there is a work around for:

    It appears that one cannot DECLARE a temporsry table and trhen DECLARE a cursor to select from that temporary table in a stored procedure. Here's my SQL:

    DECLARE GLOBAL TEMPORARY TABLE RetrievedItem
    (ARCHIVERETRIEVALID DECIMAL(13, 0),
    AIMDOCUMENTID VARCHAR(256),
    CAPTUREDATE DATE,
    ROUTINGTRANSITNUMBER VARCHAR(9),
    MICRACCOUNTNUMBER VARCHAR(24),
    POSTINGACCOUNTNUMBER VARCHAR(24),
    LOCATION VARCHAR(24),
    SERIALNUMBER VARCHAR(20),
    AMOUNT DECIMAL(13, 2),
    ITEMTYPE VARCHAR(20),
    STATUS VARCHAR(24),
    SEQUENCENUMBER VARCHAR(24),
    SORRYDOCINDICATOR VARCHAR(20),
    APPLICATIONID VARCHAR(20),
    CREDITDEBITFLAG VARCHAR(5),
    IMAGELENGTH INTEGER,
    AVAILABLEFLAG CHAR(1)
    );

    DECLARE SELECT_CURSOR CURSOR WITH RETURN FOR
    SELECT * FROM SESSION.RetrievedItem;

    When I try to build the proc, I get :
    "An unexpected token <cursor declaration> was found follow"".

    Is there some other way to declare a temp table and then select from it without using a cursor?

    Thanks in advance...
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: Cursor Declaration

    Originally posted by ansonee


    It appears that one cannot DECLARE a temporsry table and trhen DECLARE a cursor to select from that temporary table in a stored procedure.

    That's right. I believe the declaration section of an SP, where "DECLARE CURSOR" belongs, should come before the SQL section, where DECLARE TEMPORARY TABLE should be...

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Cursor Declaration

    Wrap the Declare GTT and the corresponding DEclare Cursor in a Compoind Statemet

    Cheers
    Sathyaram


    CREATE PROCEDURE ........
    ...
    ...
    C1 : BEGIN
    DECLARE I INTEGER
    ....
    ....
    BEGIN
    DECLARE GLOBAL TEMP TABLE ....

    DECLARE GTT_CURSOR ......

    END
    END C1

    HTH

    Sathyaram




    Originally posted by n_i
    That's right. I believe the declaration section of an SP, where "DECLARE CURSOR" belongs, should come before the SQL section, where DECLARE TEMPORARY TABLE should be...
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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