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
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.
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?
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:
(col_2 + col_3)
col_3 = z_n
If I get an x instead,
(col_1 + col_2)
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.
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.
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 (
where 'z' = :test_val
and calendar_date = date -1
where 'x' = :test_val
and calendar_date = date
explain exec con_test('z');
the explain shows that only one of the SQL will be executed...
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.