09 August 2018        Add to Favorites   Report

How to pass a parameter into a sql query in Teradata


Create a Macro in Teradata. A Macro is pretty much just a parameterized view, 

CREATE MACRO myMacro (parameter1 VARCHAR(20), parameter2 DATE)
    SELECT t1.* 
    FROM table1 t1
        SELECT t2.col1, t3.col2, SUM(t3.col3)
        FROM table2 t2
        INNER JOIN table3 t3
        WHERE t3.col2 NOT LIKE :parameter1
        GROUP BY 1,2
        QUALIFY ROW_NUMBER() OVER(PARTITION BY t2.col1 ORDER BY t3.col1) = 1
    ) x
    ON t1.col1 = x.col1
    WHERE t1.START_DATE >= :parameter2;

To call it:

Exec myMacro('Harold', DATE '2015-01-01');

Read great educational content like this and a lot more ! Create my free account now 🎁