All Forums Database
MattW At ATT 11 posts Joined 06/06
27 Jun 2006
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?

dnoeth 4628 posts Joined 11/04
28 Jun 2006

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

Dieter

MattW At ATT 11 posts Joined 06/06
29 Jun 2006

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.

sethiyagu 19 posts Joined 11/04
29 Jun 2006

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

MattW At ATT 11 posts Joined 06/06
30 Jun 2006

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 withCreate 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 DISCRSNFROM 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

dnoeth 4628 posts Joined 11/04
30 Jun 2006

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 DISCRSNFROM VNTV010_CRIS_SO_NUMBER SAND S.SERVICE_ORDER_APPLICATION_DT BETWEEN :startdateAND :enddateAND 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

Dieter

You must sign in to leave a comment.