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.
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.