Archives of the TeradataForum

Message Posted: Mon, 09 Sep 2002 @ 21:04:34 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Database and table names cannot be parameterized. However, if one does not mind coding the same request with different table names, one could pass a "bit mask" argument to choose which tables return rows to the UNION. Like so:

REPLACE MACRO DSCDVP01.psfcnt
  (
    TBLSEL DECIMAL(3)
  )
  AS (
        LOCK DSCDBP01.DSCTB105 FOR ACCESS
        LOCK DSCDBP01.DSCTB106 FOR ACCESS
        LOCK DSCDBP01.DSCTB107 FOR ACCESS
        select count (*) from dscdbp01.dsctb105 where ((:tblsel/1) mod 10) EQ 1
        UNION
        select count (*) from dscdbp01.dsctb106 where ((:tblsel/10) mod 10) EQ 1
        UNION
        select count (*) from dscdbp01.dsctb107 where ((:tblsel/100) mod 10) EQ 1;
  )
;

EXEC PSFCNT (000); /* RETURNS NO ROWS */
EXEC PSFCNT (001); /* RETURNS TABLE 105'S ROWS */
EXEC PSFCNT (011); /* RETURNS TABLE 105 AND 106'S ROWS */
EXEC PSFCNT (111); /* RETURNS TABLE 105, 106, 107'S ROWS */
EXEC PSFCNT (101); /* RETURNS TABLE 105 AND 107'S ROWS */
EXEC PSFCNT (100); /* RETURNS TABLE 107'S ROWS */
et cetera.

One could keep one copy of the request, and use a text editor to create the sections of the macro.

	<Prev	Next>		<<First	<Prev	Next>	Last>>