Using Parameters with Queries

Database
Enthusiast

Using Parameters with Queries

I need to write a query that requires a different WHERE clause each time it is run. I would normally do this in other systems by using a parameter value, which is then used as the criteria. How do I specify the use of a parametr in a Teradata query?
14 REPLIES
Teradata Employee

Re: Using Parameters with Queries

Are you going to use it in a Stored-Procedure?

Regards,

Adeel
Enthusiast

Re: Using Parameters with Queries

No, in a query running from Query Assistant looking at DW views.
Teradata Employee

Re: Using Parameters with Queries

Can you please give some sort of example?

And are you talking about parameterized-views?

Regards,

Adeel
Enthusiast

Re: Using Parameters with Queries

An example might be:

SELECT col1, col2, col3
FROM viewa
WHERE col1 = (parameter value typed in by me each time I run the query)
Teradata Employee

Re: Using Parameters with Queries

You can use parameterized-macro for this.

Example:

CREATE MACRO mac1 (Col1Value INTEGER)
AS
(
SELECT col1, col2, col3
FROM viewa
WHERE col1 = :Col1Value;
);

EXECUTE mac1(1);
EXECUTE mac1(2);

HTH.

Regards,

Adeel
Enthusiast

Re: Using Parameters with Queries

Tanks for this, it is much appreciated. However, I do not have rights to create macros, or anything else for that matter! Can this not be done just using the SELECT part of the statement?
Teradata Employee

Re: Using Parameters with Queries

I don't think so, as a matter of fact SQL does not take input variable by itself!!

Regards,

Adeel
Fan

Re: Using Parameters with Queries

In Teradata you can use ? as parameter input

An example might be:

SELECT col1, col2, col3
FROM viewa
WHERE col1 = ?col1
Enthusiast

Re: Using Parameters with Queries

Your SQL will work in SQL Assistance not in BTEQ

Another alternative....

create a table with one column and insert a value and join this table with your actual table and simple run the view...
you need to delete and insert new value in the new table before you run your view for new value

Syed