I have searched high and low on the forums and Google and perhaps my choice of words to search on covers too many topics. Let me explain my situation:
I would like to use a macro to create an easier user interface to a baseline data pull that is frequently used. Sometimes I am interested in limiting a parameter/column other times I would like the column to include all possible choices. My interest in using the macro is only having to put in the variables of interest in the execute statement. Let me use the example of a mailing_address database.
Example one: I would like all entries where the State column = ‘PA’
Execute mailing_address (state = ‘PA’)
Example two: I would like all the entries where street = ‘Walnut’
Execute mailing_address (street = ‘Walnut’)
In the where statement of my macro I plan on using the following.
REPLACE MACRO mailing_address(
street CHARACTER(30)default ????????,
state CHARACTER(2) default ????????,) AS
FROM database_db.address_table a
a.streetdb = :street
and a.statedb = :state ;);
In example one I would like all street names included and in example two I would like all 50 states. I am looking for the default “KEY WORD” to replace the question marks that will allow me to pull this off. Any help would be greatly appreciated.
Try this, street and state are optionals:
Ex. exec mailing_address('Franklin',)
REPLACE MACRO mailing_address( street CHARACTER(30)default NULL, state CHARACTER(2) default NULL)
( SELECT * FROM database_db.address_table a
:state IS NULL AND trim(a.streetdb) = trim(:street) OR
:street IS NULL AND trim(a.statedb) = trim(:state) OR
trim(a.streetdb) = trim(:street) AND trim(a.statedb) = trim(:state) ;