Legit Stack Legit Stack - 1 month ago 8
SQL Question

DB2: can't insert or select from temporary table

I have tried lots of permutations of

DECLARE
and
CREATE
but I can't display any data from my temporary table. Here's the latest iteration:

DECLARE GLOBAL TEMPORARY TABLE session.temporary_table (ACTNO CHAR);
INSERT INTO session.temporary_table VALUES ('1'), ('2');
SELECT * FROM session.temporary_table;
SELECT DISTINCT ACTKWD FROM JMILLER.ACT INNER JOIN session.temporary_table ON ACT.ACTNO = session.temporary_table.ACTNO;
DROP TABLE session.temporary_table;


The first
SELECT
is to see if there's anything in the
temporary_table
. Which there doesn't seem to be.

enter image description here

Even though when I test the query in this tool it says every line of it completed correctly.

enter image description here

What am I doing wrong in my sql statements? I was having trouble with permissions earlier but that seems to be fine now and I'm not getting any errors, so it must be in my code.

Answer

You did not add the clause ON COMMIT PRESERVE ROWS.

Also see if you could make things simpler by using CTE (the WITH statement)

Comments