How to create 'Macro' in netezza sql

Question

I have to create several tables using the same commands except using different year's of data. For example:

    create temp table test_2002 as
    select * 
    from claim
    where purchase_year = '2002';

    create temp table test_2003 as
    select * 
    from claim
    where purchase_year = '2003';

    .....

I want to create 10 tables, such as test_2002, test2003, test2004, etc. But I don't want to repeatedly write 10 times. So is there a way to simplify the process, like write a macro?

Thanks in advance!

score 1 · Answer 1 · edited May 23 '17 at 10:27

1

Even if there were a way (I don't know of one), this seems like more of a task for a text editor. Check out

Or, if you're really desperate, just use this formula in excel after dragging 10 lines of years.

="create temp table test_" & A1 & " as select * from claim where purchase_year = '" & A1 & "';"

edited May 23 '17 at 10:27

Community

1
1

answered Aug 24 '15 at 12:27

Jeremy Fortune

2,459
1
18
21

ScottMcG · Accepted Answer · 2015-08-26T05:41:39.607

If you can't use a nice text editor's macro functions to generate text (which is what you should probably be doing), and absolutely have to do it entirely with something you can call from a SQL interface on Netezza, then a stored procedure is your only hope.

Here is a sample stored procedure...

CREATE OR REPLACE PROCEDURE SP_CREATE_LOOP(INTEGER, INTEGER)
RETURNS INTEGER
LANGUAGE NZPLSQL AS
BEGIN_PROC
DECLARE
    pStartVal ALIAS FOR $1;
    pCount ALIAS FOR $2;    
    vSQL varchar(30000);

BEGIN

for i in 1 .. pCount LOOP
    vSQL := 'CREATE TABLE CLAIM_' || pStartVal + i-1 || ' as SELECT * from CLAIM WHERE PURCHASE_YEAR = ' || pStartVal + i-1 || ';';
    EXECUTE IMMEDIATE vSQL;
END LOOP;

END;
END_PROC;

..and what it creates.

TESTDB.ADMIN(ADMIN)=> \d
       List of relations
 Schema | Name  | Type  | Owner
--------+-------+-------+-------
 ADMIN  | CLAIM | TABLE | ADMIN
(1 row)

TESTDB.ADMIN(ADMIN)=> exec SP_CREATE_LOOP(2000,5);
 SP_CREATE_LOOP
----------------

(1 row)

TESTDB.ADMIN(ADMIN)=> \d
          List of relations
 Schema |    Name    | Type  | Owner
--------+------------+-------+-------
 ADMIN  | CLAIM      | TABLE | ADMIN
 ADMIN  | CLAIM_2000 | TABLE | ADMIN
 ADMIN  | CLAIM_2001 | TABLE | ADMIN
 ADMIN  | CLAIM_2002 | TABLE | ADMIN
 ADMIN  | CLAIM_2003 | TABLE | ADMIN
 ADMIN  | CLAIM_2004 | TABLE | ADMIN
(6 rows)

You can find the documentation for Stored Procedures in Netezza here.

How to create 'Macro' in netezza sql

2 Answers2