Executing Macro's with a WHERE Clause

[johannesd]

I need to do something like the following :

EXECUTE MACRO m1 WHERE EXISTS (SELECT 1 FROM t1);

Since SQL does not support the statement above, I initially thought I should probably get away using ActivityCount, but the number of macro's involved etc.. makes this a little messy to code... I was wondering if there might be an easier solution out there... Any ideas? Thanks!

 

[tdatgod]

Hi,
what is the Macro M1 trying to do? are you trying to execute the macro on every selected row in t1 individually?

Can you put the where clause in the macro?

 

[Guest_imported]

Table t1 is a very small table that controls which macros (in a set of 45 or so macros) needs to be run. Table t1 has 2 columns, macroname and date. I only want to run a macro if the macroname exists in t1 with the current run date.

I thought of putting the WHERE clause in the macro itself, but since there are so many macros involved, I was hoping to do the WHERE clause outside the macro. Also having the WHERE outside the macro, makes it easier to run all the macros, no matter what the contents of t1 is, when the need for that occurs from time to time, without having to change the macros themselves.

 

[tdatgod]

Hi,
Basically you do a select out of the table into a file and then turn around and read it back in.

( you might have to turn off column headings to make this work without errors )


something like. ( from BTEQ )

/* make sure the export file doesn't exist */
.os rm -f =/tmp/script.sql

/* create the script file with all the required macros */
.export file=/tmp/script.sql

sel 'execute', macroname, ';'
from t1
where
rundate = date;

.export reset

/* if no macros were selected jump over running them */
.IF ACTIVITYCOUNT<1 THEN .GOTO NO_MACROS_TODAY

/* Run the generated SQL */
.run file=/tmp/script.sql

.LABEL NO_MACROS_TODAY


No you could probably forgo the activity count since if nothing was selected the file would be empty but I would rather not have an error generated in the script.

 

[johannesd]

Thanks for all the help.