How to create a Macro passing table names as the parameter

UDA
N/A

How to create a Macro passing table names as the parameter

I am writing sql for 2 different lines of business. Everything in the code is the same except for 2 table names (where I'm selecting from and inserting into).

I would like to use the macro but it doesn't work with how I'm writing it. Any suggestions? This would help so much on maintenance becuase everything is the same except for the 2 table names.

Here's what I have so far that isn't working.
CREATE MACRO CRTCL (RPT_TABLE VARCHAR(40), SO_TABLE1 VARCHAR(40))

as
(
INSERT INTO hs_rpt_tbls.tps_work2
SELECT RP.per_end_dt, BE.rgn_no, BE.assigned_ctr, SOSP.tps_spc_cd,SOSP.tps_idy_cd,SOSP.so_typ_cd,
count(SOSP.so_dt)
FROM
:SO_TABLE1 SOSP,
(SELECT per_end_dt, acctg_yr_sta_dt from hs_db.rptg_per group by 1,2) as RP,
(SELECT rgn_no, assigned_ctr, un_no FROM hs_db.bugroup by 1,2,3) as BE
WHERE
SOSP.so_crt_dt between RP.acctg_yr_sta_dt and RP.per_end_dt
AND SOSP.un_no=BE.un_no
GROUP BY 1, 2,3,4,5,6;
);

I would execute it using the following:
EXECUTE CRTCL (‘dbname.so_table', ‘dbname.final_table);
3 REPLIES
N/A

Re: How to create a Macro passing table names as the parameter

You can't pass any database/table/column-name as parameter to macros.
The only way is a Stored Procedure using Dynamic SQL.

Dieter

Re: How to create a Macro passing table names as the parameter

Hi Dieter,

I am having requirement where I want to use database_name and table_name as parameterized in stored procedure.

In procedure I will be performing below DML operations :

INSERT INTO database_name.table_name

SELECT * FROM database_name.table_name;

UPDATE database_name.table_name

SET FLAG='Y'

WHERE DATE_1=DATE '1900-12-01';

How this can be achieved using dynamic SQL?

Thanks in advance..!!

N/A

Re: How to create a Macro passing table names as the parameter