How to pass a parameter into a sql query in Teradata

Solution:

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

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

To call it:

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

dan-irving

posted on