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?
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');
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.
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?
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.