Temporary tables and AS statement usage

Question

If I have a query, say:

SELECT *
FROM ( SELECT fields 
       FROM tables 
       WHERE condition 
     ) 
AS TEMP_TABLE

are the results of the above query saved in a temporary table called TEMP_TABLE so as I can perform another query on it later? Will the query below be executed successfully when using DB2?

SELECT fields 
FROM TEMP_TABLE 
WHERE condition

Your database may store the result as a temporary table, it may stream it, or likely in your case, it may rewrite the query without the subquery. In any case, you cannot refer to the subquery result in a later query. — Andomar, Jul 02 '13 at 08:39
No. In this case `TEMP_TABLE` is just an alias for your subquery — Andrey Gordeev, Jul 02 '13 at 08:39
I am using DB2.. ah so if I have to access the results returned from TEMP_TABLE I have to actually create a temporary table myself, insert the results with INSERT INTO and select from that table? — Bernice, Jul 02 '13 at 08:46
Check this question - http://stackoverflow.com/questions/11422572/db2-equivalent-of-tsql-temp-table — Nenad Zivkovic, Jul 02 '13 at 08:49
http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.intro%2Fsrc%2Ftpc%2Fdb2z_creationoftemporarytables.htm found this on DB2 temp tables...i dont know the syntax myself — Dev N00B, Jul 02 '13 at 09:39
@DevN00B There are 3 members of the DB2 family. DB2 for i, DB2 LUW (Linux, Unix, Windows), and DB2 for z/OS. Answers for one do not always apply to the other, and have some differences in features. Most people posting here are using DB2 LUW, seem to assume others know this, or perhaps are unaware. — WarrenT, Jul 02 '13 at 16:31

score 2 · Answer 1 · answered Jul 02 '13 at 08:49

2

The answer is NO, it's just an alias for a subquery.

If you want to use it later, you have to explicitly create it.

answered Jul 02 '13 at 08:49

Alejandro Colorado

6,034
2
28
39

score 1 · Answer 2 · answered Jul 02 '13 at 08:47

1

you can create temporary table in following way.

CREATE TEMPORARY TABLE temp_table AS (
SELECT fields 
       FROM tables 
       WHERE condition
);

then you can retrieve data from the temp table as below.

SELECT * FROM temp_table

answered Jul 02 '13 at 08:47

Chathuranga Tennakoon

2,059
1
18
20

Apparently this is not valid DB2 syntax :( Do you have any idea on what I can do in DB2? – Bernice Jul 02 '13 at 09:09
@Bernice see this question: http://stackoverflow.com/questions/11422572/db2-equivalent-of-tsql-temp-table – bhamby Jul 02 '13 at 12:38

Temporary tables and AS statement usage

2 Answers2