Volatile Tables in Macros

Database
Enthusiast

Volatile Tables in Macros

I am writing a long macro that needed to stage data in several tables before loading into final table.

I just learnt that I cannot create more than one volatile table in a macro through error "Data definition not valid unless solitary". I also cannot mix both ddl and dml in macro. Which means I cannot use volatile tables to stage data in select into final table in the same macro. That makes volatile tables unusable to use volatile tables in a macro. Please correct me if I am wrong.

For this purpose do I need to use global temp tables? Is there any other better solutions that I can use? 

Tags (1)
6 REPLIES
Senior Apprentice

Re: Volatile Tables in Macros

Hi Raghu,

any DDL request in Teradata must commited (no other SQL after it allowed) and a macro is always a MultiStatement Request, so there's no way to create and then use a Volatile Table in a macro.

But this is a use case for Global Temp Tables, you create the GTT before the macro and then it's a INSERT at the begin of the macro and after the EXECUTE the GTT's content is automatically deleted (if you use with ON COMMIT DELETE ROWS)

Enthusiast

Re: Volatile Tables in Macros

Thanks Dieter... :)

Enthusiast

Re: Volatile Tables in Macros

Note to Teradata product people.   What a joke.  This guy wants to use some temp tables in a macro to get to a result and he's done that a 1000 times before in ms sql and now he's being told he can't have a single temp table with dml in a teradata macro.  I'm pretty sure successive temp table work in Oracle too.  My boss used temp tables to make our company what it is, and it's a company you've heard of.   So get with the program teradata and get your parsers into the 20th century (I won't ask for the 21st) with your next release.  I won't even talk about my problem which is how to get a dot.net call to teradata to use a temp table when each invocation of Teradata is a new session.

Senior Apprentice

Re: Volatile Tables in Macros

MS SQL Server doesn't have Macros and the same for Oracle :)

If you want to create a Volatile Table and use it simply switch to a Stored Procedure instead.

Why updating very old syntax (Macro) if the new syntax is already fully implemented (SP)?

And your problem with Temp Tables will be the same in other DBMSes, a temporary table is session local, when the session ends, it's automatically dropped. Blame Standard SQL for it.

Why do you close the session instead of keeping it open?

KKT
N/A

Re: Volatile Tables in Macros

So can I put below code in SP and get result set out when run the Proc without a cursor.

 CREATE MULTISET VOLATILE TABLE VT_COMP
AS
(
SELECT COMPANY_ID, COMPANY_NAME
FROM COMPANY
)
WITH DATA
UNIQUE PRIMARY INDEX (COMPANY_ID)
ON COMMIT PRESERVE ROWS;

SELECT * FROM VT_COMP ;

Teradata Employee

Re: Volatile Tables in Macros

Yes possible.