Macro default values for where statements

Database

Macro default values for where statements

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
 (
 SELECT
 *

 FROM database_db.address_table a

 WHERE
  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.

Tags (1)
1 REPLY
N/A

Re: Macro default values for where statements

Hi Keith,

Try this, street and state are optionals:

Ex. exec mailing_address('Franklin',)

     exec mailing_address(,'CA')

     exec mailing_address('Franklin','CA')

REPLACE MACRO mailing_address(   street CHARACTER(30)default  NULL,   state  CHARACTER(2) default  NULL) 
AS
( SELECT *  FROM database_db.address_table a 
  WHERE
   :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) ;
);

Regards