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

Answer 1

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!).