Create a macro that chooses from teo different select staments depending on a given parameter.

General
Fan

Create a macro that chooses from teo different select staments depending on a given parameter.

Hello everyone, I am almost completlely new to teradata a and sql. I am seasoned in C, C++, Java and other languages but not until now started using a database language. Anyways, my question is this; is there a way to create a macro or something similar where given the varialbe var_1 then it would run the correct select stament. Something like this:

replace macro CHOOSE(var_1 datatype) AS (

if var_1 = x

select .....

else

select ....

)

I know there is the case stament that serves as and if then clause inside a select statement but if I could do something like what I have described above would safe me a lot of time.

I hope I was clear in my questions if there is any need for more explanation please let me know. Thanks in advance.

Tags (1)
5 REPLIES
Senior Apprentice

Re: Create a macro that chooses from teo different select staments depending on a given parameter.

Sounds like you want a Stored Procedure, a Teradata macro can't do that.

But as a newbie your DBA will probably not grant you the right to create SPs as you can do too many bad things in a SP.

Can you show more details what you're trying to achieve? What client do you use, how will you call that macro?

Fan

Re: Create a macro that chooses from teo different select staments depending on a given parameter.

Hello Dieter,

Thanks for the quick response. We use Teradata SQL Assistant, version 14.01.0.05, and provider ODBC 14.00.00.04

So basically what I have is a table that has columns that look like this:

col_1   | col_2 | col_3

x1            y1        z1

x2            y2        z2

:               :           :

xn            yn-1        zn-1    

xn             yn         zn

Say they're all integers. So if I am given a z I need to do the following query:

sel
  col_1,
  (col_2 + col_3)
from
  TABLE
where
  col_3 = z_n
;

If I get  an x instead,

sel  
  col_3
  (col_1 + col_2)
from  
  TABLE
where 
  col_1 = x_n
;

Since I have to do this several times a day I was hoping there was a way to create a macro that takes the integer and an indicator to say if it is an x or z and do the right select statement.

I hope I have explained myself a little better. I think I would call this macro OpChooser or TimeSaver hehe. Anyways, any help would be greatly appreciated. Thanks again.

Teradata Employee

Re: Create a macro that chooses from teo different select staments depending on a given parameter.

Best options seems Stored Procedure. You can manage conditional logic very solidly in SPs. Macros doesnt support such functionality.

You can also achieve this using bteq/shell script combo.

HTH!

Supporter

Re: Create a macro that chooses from teo different select staments depending on a given parameter.

SP might be the better way but in case there are limitations on SP and both SQLs deliver the same column lists and data types you can try something like

replace macro con_test (test_val char(1)) as (
select *
from sys_calendar.calendar
where 'z' = :test_val
and calendar_date = date -1
union all
select *
from sys_calendar.calendar
where 'x' = :test_val
and calendar_date = date
;
);

exec con_test('z');
exec con_test('x');
explain exec con_test('z');

the explain shows that only one of the SQL will be executed...

Teradata Employee

Re: Create a macro that chooses from teo different select staments depending on a given parameter.

If select list would be same, Macro could have solved the issue .... but in the example they are not. SP would be a better [only] option with seperate columns selected and changed WHERE clauses.

HTH!