Teradata Volatile Table Statement is not creating any rows

Question

I want to create table in Teradata. Therefore I am using this syntax:

    CREATE VOLATILE TABLE a AS
    (
        Select * FROM ...
    ) WITH DATA PRIMARY INDEX ( ACCOUNT_ID )
;

The inner SELECT statement results in 4 rows. However, when I run the entire query, the resulting data set does not have any rows. Strange, I know - that`s why I'm writing. Please help. Thanks.

Answer 1

You need to include the ON COMMIT PRESERVE ROWS option with your DDL for the volatile table:

   CREATE VOLATILE TABLE a AS
    (
        Select * FROM ...
    ) WITH DATA 
    PRIMARY INDEX ( ACCOUNT_ID )
    ON COMMIT PRESERVE ROWS;

The default COMMIT mode for volatile (and global temporary) tables is to DELETE the rows at the end of the transaction.