Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2010
    Posts
    17

    Smile Unanswered: Declare global temporary table

    This declares a table that persists for the session.
    I have need of a table for just the duration of an SP call really - but no matter this would do.

    Question:
    Can I use this declare statement in a procedure that is called more than once per session or do I need a special procedure that is only called once at start-up in which to declare the temporary table?


    Many thanks

    10Pints

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can declare it in the stored procedure that is called more than once, just use the "WITH REPLACE" clause.

    Andy

  3. #3
    Join Date
    Nov 2010
    Posts
    17
    Ah so - many thanks Andy!
    Much appreciated.
    10Pints

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Another question is what you want to do with the table. Often I have seen something like this:
    Code:
    DECLARE GLOBAL TEMP TABLE t ...;
    LOOP ...
        INSERT INTO t VALUES (...);
    END LOOP;
    DECLARE c CURSOR WITH RETURN FOR SELECT * FROM t;
    In such a situation, you can often get along by avoid the temp table completely:
    Code:
    DECLARE c CURSOR WITH RETURN FOR SELECT * FROM TABLE ( VALUES (...), (...), ... ) AS t
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Nov 2010
    Posts
    17
    Sorry for tardy reply - been away.
    Yes that would be useful in many situations: I will bank that one

    Many thanks

    10Pints

Tags for this Thread

Posting Permissions

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