How do you declare a variable (specially a date) in Teradata?

General
Junior Supporter

How do you declare a variable (specially a date) in Teradata?

Hello, I'm new to Teradata and I'm curious in how to declare a variable in Teradata. In SQL server, I have the following code to declare a start and end date, but I'm not sure what the Teradata equivalent would be:

 

SQL SERVER code:

DECLARE @ANCHORDATE DATETIME

SET @ANCHORDATE  = DATEADD(DAY,-1, '01 JANUARY 2017')

DECLARE @START_DATE DATETIME

DECLARE @END_DATE DATETIME

SET @START_DATE=DATEADD(YEAR,-3,@ANCHORDATE)+1

SET @END_DATE=@ANCHORDATE

9 REPLIES 9
Junior Supporter

Re: How do you declare a variable (specially a date) in Teradata?

Hi,

If you are talking about a SP, then you do it this way.

DECLARE  TGTDB             VARCHAR(30)     DEFAULT  '';

--select some value to this variable from a select

  SELECT databasename   INTO :TGTDB
    FROM dbc.databases
   WHERE databasename   = :TGTDBNAME

--check for some value in it

IF ( SRCDBNAME =  '' ) THEN 
     SET MESSAGE = 'ERROR: Source DB  name cannot be empty' ;
     LEAVE StoredProcedure;
  END IF;

For date also, you can declare a char/varchar and it can be used in queries.

--Samir

Junior Supporter

Re: How do you declare a variable (specially a date) in Teradata?

What is SP? In my sql server code I eventually reference @START_DATE and @END_DATE with something similar to this:

 

SELECT *
FROM  TEST
WHERE TEST_DATE BETWEN @START_DATE AND @END_DATE

Junior Supporter

Re: How do you declare a variable (specially a date) in Teradata?

Hear another example declare/set I wrote in SQL server in the past, but I do not know what the Teradata syntax equivalent is:

 

DECLARE @START_DATE DATETIME

DECLARE @END_DATE DATETIME

SET @END_DATE = DATEADD (DAY, -1, '1 AUGUST 2015')

SET @START_DATE = DATEADD(MONTH, -12,@END_DATE)

SELECT @START_DATE AS [START DATE]

SELECT @END_DATE AS [END DATE]

 

SELECT DISTINCT *

FROM TEST

WHERE TEST_DATE between @START_DATE AND @END_DATE

Teradata Employee

Re: How do you declare a variable (specially a date) in Teradata?

you can only do this in stored procedures (SP's)

 

declare the variable at the beginning of procedure:

DECLARE lastBatchDTTM TIMESTAMP(3);

 

modify variable value just like in SQL Server:

lastBatchDTTM = current_timestamp(3);

 

 

Visitor

Re: How do you declare a variable (specially a date) in Teradata?

Hi.

 

Only to confirm, you are telling we only can declare variables inside a Stored Procedure?

It means everytime I need to use a variable in Teradata, I have to create a Stored Procedure to do that?

 

If the answer is "yes", there is any tip to avoid this?

 

Thank you.

Teradata Employee

Re: How do you declare a variable (specially a date) in Teradata?

It really depends on the use case...if you want to be able to modify the value of the variable during execution then yes, I'm not sure how you'd do that outside of a stored procedure.  If you simply want to pass a specific value at runtime and that variable isn't modified during runtime, then you can use a parameterized macro:

 

REPLACE MACRO get_data (proc_date DATE) AS(
SELECT * FROM TableA WHERE trans_date > :proc_date);

 

To call:

EXECUTE get_data('2019-1-21')

This would return a dataset like any other SQL query.

 

Or...

In TD Studio, if you just want to use a paramaterized value, you can use a "?" and Studio will ask you to supply a value every time you run the code...

SELECT * FROM TableA where trans_date > ?

Teradata Employee

Re: How do you declare a variable (specially a date) in Teradata?

Teradata does not offer variables in the same manner as SQL Server. However, you can use single-row-single-column volatile tables in an equivalent manner.

 

Here is your example rewritten in Teradata SQL to use volatile tables and scalar subqueries:

 

create volatile set table start_date (c1 date) on commit preserve rows;
create volatile set table end_date (c1 date) on commit preserve rows;
insert into end_date values (date '2015-08-01' - interval '1' day);
insert into start_date values (add_months ((select * from end_date), -12));
select distinct * from test where test_date between (select * from start_date) and (select * from end_date);

 

Teradata Employee

Re: How do you declare a variable (specially a date) in Teradata?

I'll neatpick a bit here but an I/O is an I/O.

I'd write tomnolan's code like this :

create multiset volatile table mvt_param, no log
( id_param     byteint                           not null
, dt_param     date format 'yyyy-mm-dd'  not null
)
unique primary index (id_param)
on commit preserve rows;

insert into mvt_param values (1, date '2015-08-01' - interval '1' day);
insert into mvt_param values (0, add_months(date '2015-08-01' - interval '1' day, -12));

select *
  from test
 where test_date between (select dt_param from mvt_param where id_param = 0)
                     and (select dt_param from mvt_param where id_param = 1);

You can also grab a step using a period datatype instead two rows :)

 

Oh and this syntax is also allowed by Teradata, which make it kinda "variable like" :

select *
  from test
 where test_date between start_date.c1 and end_date.c1;

But I'm not fond of it, hide joins and such.

Highlighted
Junior Contributor

Re: How do you declare a variable (specially a date) in Teradata?

Best case is to avoid I/Os :-)

 

replace view my_params as
select date '2015-08-01' as start_date,
          date '2015-09-01' as start_date;

select *
from test
where test_date between my_params.start_date and my_params.enddate

 

 

I showed several ways in an old blog:

Global and session level parameters in SQL

 

Finally in TD16+ the QueryBand approach works fine using the getQueryBandValue.