There is no direct answer to this question as this concept is present and used only in Stored Procedures and Macros in Teradata. But there are ways to replicate this functionality in the command prompt.

As said before create a macro and pass a variable. Remember you can not use dynamic SQLs in a Macro. Also you can create a volatile (temporary) table which lives until the session is active, so here is the code on how to create a volatile table. Suppose you want to hold today’s date in a temp variable/table here is the way to do it.

CREATE VOLATILE TABLE today_date AS

(SELECT DATE) WITH DATA ON COMMIT PRESERVE ROWS;

In case you do not want to see the results after you run the query, then use DELETE in place of PRESERVE which deletes the row as soon as the query finishes. This is helpful if you are doing any transactional query.

If you are wondering how to create macro, here is the way.

CREATE MACRO macro_name (<param1> datatype, <param2> datatype) AS

([INSERT…;][UPDATE…;][DELETE…;][SELECT…;] );

For example;

CREATE MACRO my_macro( InVal INTEGER)

( Update mytable set mycolumn = mycolumn *5/100

WHERE myothercolumn = :InVal;

SELECT mycolumn, myothercolumn

From myothertable

WHERE  myothercolumn = :InVal);

How to execute/run this Marco, here is how;

EXECUTE (EXEC) <macro_name> [parameter value list]

For Example; EXEC my_macro ( 4545);EXEC my_macro ( InVal = 4545); 

If you have more than one parameters and using name for value assignment, the sequence of the parameter in the EXEC does NOT matter.

What is equivalent to Oracle’s Dual table in Teradata? 

Good question, actually there is no such table present in Teradata. But the same functionality can be derived by the following code. Before that just to remind that the Dual table in Oracle is used for intermediate calculations, like selecting a date and calculating something. In Teradata it is done without the use of any such table.

In Oracle if you are using

SELECT SYSDATE FROM DUAL

In Teradata the same can be achieved by the following shorter command;

SELECT DATE

Isn’t it COOL!

How to Change your Password in Teradata

This command is pretty similar to the command in Oracle. 

MODIFY USER userid AS PASSWORD = yournewpassword

Our Random Articles

More Links