Creating a volatile table and selecting from it in the same query.

Database
Enthusiast

Creating a volatile table and selecting from it in the same query.

Is it possible to create a volatile table and then select from it in the same query?

For example:

create volatile table TempTable1 as(select a,b,c

from table1) with data ON COMMIT PRESERVE ROWS;

select * from TempTable1;

throws the following error:

Executed as Single statement.  Failed [3722 : HY000] Only a COMMIT WORK or null statement is legal after a DDL Statement. 

1 REPLY
Enthusiast

Re: Creating a volatile table and selecting from it in the same query.

Are you trying to create the volatile table and select from it in the same unit of work?

If so, you can't do that as only one DDL statement is allowed in a unit of work. So, the SELECT statement has to be a separate unit of work.

You can do this as a derived table where you select from a "table" created using another query:

select * from

  (select a,b,c

   from table1) dt1;