All Forums Database
BowlingKaz 5 posts Joined 12/05
08 Jul 2008
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?

Adeel Chaudhry 773 posts Joined 04/08
08 Jul 2008

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

-- If you are stuck at something .... consider it an opportunity to think anew.

BowlingKaz 5 posts Joined 12/05
08 Jul 2008

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

Adeel Chaudhry 773 posts Joined 04/08
08 Jul 2008

Can you please give some sort of example?And are you talking about parameterized-views?Regards,Adeel

-- If you are stuck at something .... consider it an opportunity to think anew.

BowlingKaz 5 posts Joined 12/05
14 Jul 2008

An example might be:SELECT col1, col2, col3FROM viewaWHERE col1 = (parameter value typed in by me each time I run the query)

Adeel Chaudhry 773 posts Joined 04/08
14 Jul 2008

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

-- If you are stuck at something .... consider it an opportunity to think anew.

BowlingKaz 5 posts Joined 12/05
14 Jul 2008

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?

Adeel Chaudhry 773 posts Joined 04/08
14 Jul 2008

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

-- If you are stuck at something .... consider it an opportunity to think anew.

Dani 3 posts Joined 04/06
14 Jul 2008

In Teradata you can use ? as parameter inputAn example might be:SELECT col1, col2, col3FROM viewaWHERE col1 = ?col1

syed_td 9 posts Joined 07/08
14 Jul 2008

Your SQL will work in SQL Assistance not in BTEQAnother 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 valueSyed

BowlingKaz 5 posts Joined 12/05
09 Sep 2008

Thank you all for your input, it has been greatly appreciated. I believe the ? option is going to be the best for me as I can't create tables, macros views etc. Again, thank you all.

muji316 3 posts Joined 04/12
18 Apr 2012

For teradata SQL Assistant you can get the user input or parameter at run time using the '?' keyword.

For example, the below query when run would ask for MSISDN as input on runtime.

select * from CUSTOMER_MSISDN where MSISDN = '@inputMSISDN'

 

Regards,

Syed Mujtaba

Regards,
Syed Mujtaba Ahmed

muji316 3 posts Joined 04/12
08 Oct 2012

Use '?' as parameter.
select * from CUSTOMER_MSISDN where MSISDN = '?inputMSISDN'

Regards,
Syed Mujtaba Ahmed

You must sign in to leave a comment.