User Defined Variables

Database

User Defined Variables

Hi all,

In MySQL you can set a user defined variable like this:

set @varname = 'does_this_work';

select @varname;

How in the world is this done using TD?
and yes, I am new to TD.

Thanks Lau.
3 REPLIES
Enthusiast

Re: User Defined Variables

There isn't a direct equivalent to that syntax in Teradata. Check out this blog post by Dieter for an interesting example of how to do something similar though:

http://developer.teradata.com/blog/dnoeth/2011/03/global-and-session-level-parameters-in-sql

Re: User Defined Variables

thank you mnylin,
instead of making tmp tables as suggested in the blog, I have decided to wrap the small syntax into a stored procedure where I can have the user defined variables. lau

Re: User Defined Variables

Another way is creating a volatile table and refering to that table within the rest of the code.

CREATE VOLATILE TABLE VAR AS
(
SELECT '2011/01/01' AS STARTDATE
, '2011/01/31' AS ENDDATE
)
WITH DATA PRIMARY INDEX (STARTDATE, ENDDATE)
ON COMMIT PRESERVE ROWS;

SELECT *
FROM MY_DB.MY_TABLE
WHERE MY_DATE BETWEEN VAR.STARTDATE AND VAR.ENDDATE;

The advantage of this is you can refer to those variables from any bit of code within the session, however you need to be careful you aren't using multiple bits of code in multiple windows that have common variable names (such as startdate and enddate) but with different values.

The same does NOT work with Common Table Expressions.