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
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.
Matt,Could you please post the T-SQL code, I am interested to learn how its working.ThanksStration
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
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
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?