Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Macro to loop thru different databases
 
From:   Calvin W. Kline, jr.

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023