CODEWITHSUNDEEP
sqlparameter-passingteradata
user2395059
user2395059

Reputation: 80

How to pass a parameter into a long query in Teradata

I have a somewhat large query (~8 tables being joined, 100 lines of sql or so) that I use frequently in order to join many different data sources together. I would like to be able to pass a parameter into the query so that myself and other members of my team can work off the same code base and just change the time period that we are looking at.

Example code (obviously my actual code is more involved than this, but these are some of the things that I need to do):

SELECT t1.* , x.col1, x.SUM_col3
FROM table1 t1
LEFT JOIN
(
    SELECT t2.col1, t3.col2, SUM(t3.col3) as SUM_col3
    FROM table2 t2
    INNER JOIN table3 t3
    ON t2.PI = t3.SI
    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

Solutions I have considered:

  1. Using the '?' prefix in order to enter the parameters at run time. I find this method to be quite inefficient as the entered parameter is retained in the code after running the code.
  2. Using Dynamic SQL to create a procedure which can then be called. I'm struggling to get this method to work as its intended purpose seems to be relatively short queries.

How can I best structure my query and code in order to be able to run something like CALL MY_QUERY(:parameter1,:parameter2) in such a way that it either creates the resulting table in memory (less preferred) or returns a result set that I can then store or use myself (more preferred).

Upvotes: 1

Views: 12395

Answers (1)

JNevill
JNevill

Reputation: 50019

What you are wanting is a Macro in Teradata. A Macro is pretty much just a parameterized view, which is exactly what you are talking about here.

CREATE MACRO myMacro (parameter1 VARCHAR(20), parameter2 DATE)
AS
(
    SELECT t1.* 
    FROM table1 t1
    LEFT JOIN
    (
        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:

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

Upvotes: 4

Related Questions