CODEWITHSUNDEEP
sqlfunctiondb2
Alex. jpeg
Alex. jpeg

Reputation: 11

IBM DB2 store the query results in a variable

I am trying to write a function and I need to store some table in a variable. I don't have the type 'Table' I tried to write something like this:

declare t table(a integer, b integer);
set t = (select A, B from MyTable);

It does not work.It says there is a syntax error and that there is no table type. What is the solution? Is there any other way to store the query results in a variable?

Upvotes: 0

Views: 803

Answers (1)

leftjoin
leftjoin

Reputation: 38335

Use declared temporary table:

DECLARE GLOBAL TEMPORARY TABLE session.table_name (a integer, b integer) AS 
(select A, B from MyTable) WITH DATA

The declared temporary table description does not appear in the system catalog. It is not persistent and cannot be shared with other sessions. Each session that defines a declared global temporary table of the same name has its own unique description of the temporary table. When the session terminates, the rows of the table are deleted, and the description of the temporary table is dropped.

Upvotes: 2

Related Questions