Global temp OR Derived table


Global temp OR Derived table

the query which im executing is very i need to create a global temp table or derived table...

how do i do it...

do i need to include the database name before the table name??

for example...for the following to i create a temp table or derived table....

sel e.firstname, c.year, e.month, sum(c.salary)

from employers e

join customers c

on c.salary_key = e.salary_key

and salary like any (1%, 2%, 3%)

group by 1,2,3

Also, do i need to run the gtt every time before running the main query in which gtt is included??

-Thank you

Re: Global temp OR Derived table


Global temp table/Volatile table will be created in the spool space which is allocated to the logon user ID, so DB name is not required to mention for this.

The data loaded in this table is valid only for that particular logon session, so you need not rerun temp table query until your session got terminated.

To know more on limitations and syntax of GTT/Volatile table do refer SQL Data Definition PDFs in


Re: Global temp OR Derived table

Global Temporary Tables are created in user's 'temporary space', so user must have some temporary space allocation to materialize GTT. Also, base definition of GTT is stored in the Data Dictionary, which not in case of Volatile table