Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2003
    Posts
    8

    Unanswered: Temp Table question

    I'm trying to create a temp table in a SQL stored procedure on iSeries V5R1. I'm doing something like this:

    BEGIN

    DECLARE C1 CURSOR WITH RETURN FOR SELECT * FROM QTEMP.TEMP;
    Create table QTEMP.TEMP LIKE LIBRARY.TABLE;
    Insert into QTEMP.TEMP Select * from LIBRARY.TABLE where COLUMN = VENDORIDIN;
    OPEN C1;
    SET RESULT SETS CURSOR C1;

    END

    This works but has a couple of issues:

    1) After the stored procedure is finished the temp table is still out there for 30 or seconds.

    2) If I call a different stored procedure that tries to access the same temp table during that 30 seconds, it will find it.

    What I want to do is like in SQL Server where I can create a work table like using #tmpTable that only exists for that stored procedure call and is not available to other stored procedures.

    We are using VB.NET and HitSoftware OLEDB/AS400 as the data driver. Also, we are using the same UserID and Password to connect to the AS400 but are closing all connections after each call.

    Does the AS400 not provide the same temp table functionality like we can do on SQL Server or I'm I doing something wrong?

    Thanks,
    Chris

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Chris,

    Use DECLARE GLOBAL TEMPORARY TABLE

    These tables are only valid for the session where they run.

    HTH

    Andy

  3. #3
    Join Date
    Feb 2003
    Posts
    8
    I need to verify it but I thought that it was only available in V5R2, not V5R1.

Posting Permissions

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