How to create ot replace table

Question

Is there any way in teradata to

CREATE OR REPLACE VOLATILE TABLE 
AS
(
SELECT 

)

I need to make sure that it does not stop if the table already exists

Answer 1

As a Volatile Table is only visible within the current session you should know which tables have been created.

Otherwise i would simply try a DROP TABLE first and ignore a possible "object does not exist" error.

Answer 2

Short answer: No. Volatile table definitions are not stored in the data dictionary table DBC.Tables. Unlike a Global Temporary table the instantiation of a volatile table is also not tracked by a dictionary table in DBC.TempTables.

Longer answer: You can but it depends on where your SQL is executing.

You can check simply check for the existence of the table using something like SELECT 1 FROM {VolatileTableName} WHERE 1=0; or SELECT 'X' FROM {VolatileTableName] GROUP BY 1; and trap the error if the object doesn't exist. If you don't get an error the object exists. Without knowing where your code is running (BTEQ, Stored Procedure, Macro) it is difficult to explain the appropriate error handling.