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?
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)
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.
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?
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
SELECT COMPANY_ID, COMPANY_NAME
) WITH DATA
UNIQUE PRIMARY INDEX (COMPANY_ID)
ON COMMIT PRESERVE ROWS;
SELECT * FROM VT_COMP ;