declare variable in teradata

Question

In TSql I can define a variable like so:

DECLARE @Threshold AS int;
SET @Threshold = 5;

I can then use it like so:

,COALESCE(
    CASE WHEN X >= @Threshold THEN A ELSE NULL END
    ,CASE WHEN Y >= @Threshold THEN B ELSE NULL END
) AS Bla

is something similar possible on TeraData

Check http://developer.teradata.com/blog/dnoeth/2011/03/global-and-session-level-parameters-in-sql, but don't try to use the QueryBand approach, it's still flawed. — dnoeth, Apr 19 '17 at 20:42

JNevill · Accepted Answer · 2019-01-03T20:37:27.220

It's pretty common to load variables into a CTE and reference the CTE in your SELECT statement:

WITH variables AS
(
    SELECT 5 as thresholdmin, 10 as thresholdmax
)
SELECT CASE WHEN 6 BETWEEN thresholdmin and thresholdmax then 1 ELSE 0 END as thresholdCheck 
FROM variables

You can just cross join that CTE into your other tables (or derived tables) and make the same reference much like the Volatile table answer above, but without the bloat of actual storage.

An example using the sys_calendar:

WITH variables AS
(
    SELECT DATE '2016-01-01' as minDate, DATE '2016-01-31' as maxDate
)
SELECT cal.calendar_date 
FROM sys_calendar.calendar cal, variables
WHERE cal.calendar_date BETWEEN variables.minDate and variables.maxDate
ORDER BY cal.calendar_date

This is a nice solution because it will work on any RDBMS that supports CTEs (so everything except for mysql --UPDATED-- mysql 8.0+ now supports CTEs!).

@BillCram's deleted comment: I think the CTE route is more synonymous with the variable usage in tsql though. A user that is handed the script only needs to change things at the very top (in the CTE declaration) instead of hunting through the sql to find `5` and change it to `6`. If you need something more robust and repeatable, then move to a Macro or a Procedure. — JNevill, Apr 20 '17 at 19:56

David Cram · Answer 2 · 2017-04-19T22:32:45.143

3

You can't create variables in a typical Teradata sql script. Instead create a volatile table and store the value of your "variable" in the table. Then query the value from your volatile table when you need to use it. It would look something like this:

CREATE VOLATILE TABLE MyVariable
(
VariableValue int
) Primary index (VariableValue)
ON COMMIT PRESERVE ROWS;

INSERT INTO MyVariable(VariableValue)
VALUES (5);

SELECT COALESCE(
    CASE WHEN X >= VariableValue THEN 'A' ELSE NULL END
    ,CASE WHEN Y >= VariableValue THEN 'B' ELSE NULL END
) 
FROM YourTable, MyVariable;

If you decide you want to put your logic inside a stored procedure, then you can DECLARE variables.

Here are the actual variable declaration rules: http://info.teradata.com/htmlpubs/DB_TTU_15_00/index.html#page/SQL_Reference/B035_1148_015K/ch08.143.035.html#ww593657

edited Apr 19 '17 at 22:32

answered Apr 19 '17 at 19:12

David Cram

768
7
11

I am aware of volatile tables but are you really suggesting to use them in a case statement? How would that work? – cs0815 Apr 19 '17 at 21:35
I updated my answer with an example. If you are used to some other database, I will admit that it does seem cumbersome. – David Cram Apr 19 '17 at 22:35
the url is not reachable @David cram – prudhvi Indana Nov 19 '19 at 20:57
A while back Teradata moved their documentation to a new web platform (docs.teratadata.com). You can find newer documentation here: https://docs.teradata.com/reader/zzfV8dn~lAaKSORpulwFMg/Y8QsjEi3AGWZVnDsiKsSVg. You might also still be able to find the original documentaion if you feed the original URL to the wayback machine search engine at https://archive.org/web/ – David Cram Nov 20 '19 at 13:35
By the way JNevill's answer is better if you only need to use the variable once. For that a CTE is best. The value of the volatile table is that it can be re-used in a multi-statement request as long as it is still in the same session. So if you end up pasting the same CTE over and over again in your script, that is when it is time to think about a volatile table. – David Cram Nov 20 '19 at 13:56
1

Like this because CTE will only work on a single statement - would need to duplicate the pattern many times in a multi-step script. – dsz Sep 09 '20 at 02:28

score 0 · Answer 3 · edited May 26 '23 at 00:56

Not sure if this has already been said but in Teradata SQL Assistant you can use a ? as a variable as well. In SQL Server you can do the below:

declare @Date as date = '2023-05-01' 
select * from dbo.dim_date where date = @Date

To do the something similar in Teradata SQL Assistant you can do the below:

select * from dim_date where date = '?date'

When you hit F5 or F9, you'll get a prompt asking for a date and once entered, the query will run.

declare variable in teradata

3 Answers3