Help with macro

Database
Fan

Help with macro

I wonder if possible to pass a long string of multiple conditions thru macro. Reason is because the combination of conditions varies upon the business rules. Another reason is unfortunately I have no permission to create procedure in TD.

-- original query condition

WHERE SLS_GRP='2305' AND BUS_NM LIKE '%FLO%' AND LOC_AT_CNTRY_NM LIKE '%UNITED%STATE%' AND CURR_MRCH_IND='Y'

-- in macro

CREATE MACRO T(MLOGIC VARCHAR(256)) AS (

....

WHERE SLS_GRP = :MLOGIC

;);

EXEC T('''2305'' AND BUS_NM LIKE ''%FLO%'' AND CURR_MRCH_IND=''Y''');

I have tested that the macro above doesn't work. Seems like the whole string is used against SLS_GRP rather than separate multi-conditions. Does anybody know how to make multiple conditions as one input variable in macro?

Thanks,

Tags (1)
5 REPLIES
Junior Contributor

Re: Help with macro

There's no way to do it in a macro.

In every DBMS you will need some Dynamic SQL to do so and a DBA will never allow that due to possible SQL Injection, e.g.

 EXEC T('''2305'' AND BUS_NM LIKE ''%FLO%'' AND CURR_MRCH_IND=''Y'' OR 1=1');
Teradata Employee

Re: Help with macro

I agree with Dieter.

You need to use the right tool for the job. If you need to vary your WHERE-clause conditions significantly, then a macro isn't the right choice for you.

Instead, you should simply execute the query itself, and vary the WHERE-clause condition.

Fan

Re: Help with macro

I thought macro won't work but hope you guys may have secret sauce to make it work... Anyway, what about procedure? May I pass the where-clause conditions to a sql_stmt and then execute it immediate? 

Enthusiast

Re: Help with macro

If you are using unix, then you can combine database script and unix script, making  different calls to parameters you want based on your conditions.

For example, in one of my previous projects, we generate DMLs querying dictionary tables based on different parameters and exported to  files on the fly. The files are then executed in the subsequent steps.

Enthusiast

Re: Help with macro

​​You should be able to define the macro with multiple parameters, and pass the values to the Macro in separate fields. For ex

 

CREATE MACRO T (SLS_GRP VARCHAR (10), BUS_NM  VARCHAR (10), CNTRY_LOC VARCHAR (10),,,) 

and EXEC T(var01, var02, var03) etc, and in the scrit you can assign each to its own variables and use in the query.