How do you declare a variable in Teradata SQL?

Database
Enthusiast

How do you declare a variable in Teradata SQL?

First of all, I am new, and I am not sure where to post basic Teradata SQL questions.

Anyway, outside of a SP, I am writing a long piece of code which I would like to use some variables in, but I can't seem to figure out how to declare a variable in Teradata SQL. Any ideas?
5 REPLIES
Senior Apprentice

Re: How do you declare a variable in Teradata SQL?

There are no variables outside of SPs in Teradata.

But there are parameters for macros, and if it's a script, you can use shell-variables in Unix.

If you provide us with more details, maybe we can show you a workaround...

Dieter
Enthusiast

Re: How do you declare a variable in Teradata SQL?

I needed a couple variables for a start date and end date for a long stretch of code to make it more easily reusable. I settled for making a single row volatile table which holds these start and end dates as fields. It is working well.
Enthusiast

Re: How do you declare a variable in Teradata SQL?

Matt,

Could you please post the T-SQL code, I am interested to learn how its working.

Thanks
Stration
Enthusiast

Re: How do you declare a variable in Teradata SQL?

Here it is in sample format:

--step 0 This step create variables so I don't have to look through the code and worry about the dates I'm working with

Create volatile Table TEMP As
(
Select
'1060401' (int) startdate,
'1060431' (int) enddate
)
with data PRIMARY INDEX (Startdate, Enddate)
ON COMMIT PRESERVE ROWS;

SELECT 'CT' STATE,
S.Blah APPLDATE,
S.CMPLDATE,
SUBSTR(S.Blahblah,1,1) ORDER_TYPE_CD,
S.CUSTYPE1,
S.YaddaYadda,
S.DISCONNECT_REASON_CD DISCRSN
FROM VNTV010_CRIS_SO_NUMBER S

AND S.SERVICE_ORDER_APPLICATION_DT BETWEEN temp.startdate
AND temp.enddate
AND S.POSTDATE >= temp.startdate --Limit to postdate after start date(date activity occured)

Hope that helps
Senior Apprentice

Re: How do you declare a variable in Teradata SQL?

Hi Matt,
looks like a part of a SP in T-SQL to have a parameterized view.

In a lot of cases you can use a Teradata macro for that. No variables/loops/if but parameters.

create macro blabla (startdate int, enddate int) as
(
SELECT 'CT' STATE,
S.Blah APPLDATE,
S.CMPLDATE,
SUBSTR(S.Blahblah,1,1) ORDER_TYPE_CD,
S.CUSTYPE1,
S.YaddaYadda,
S.DISCONNECT_REASON_CD DISCRSN
FROM VNTV010_CRIS_SO_NUMBER S

AND S.SERVICE_ORDER_APPLICATION_DT BETWEEN :startdate
AND :enddate
AND S.POSTDATE >= :startdate ;

other DML stuff;

);

exec blabla(1060401, 1060431);

Be carefull, a macro is always a multistatement (i.e. a single transaction), which might be positive (faster performance) or negative (e.g. huge transient journal if there are several inserts/deletes/updates)

Dieter