One of our analysts, who has some experience in UNIX scripting, was wondering if TDP supports global paramerters/variables. Ideally, she would like to do something like this:
@str = (1, 6, 8)
@begdt = ‘2012-01-01’
@enddt = ‘2013-01-01’
select sales
from sales_table
where storenumber in @str
and date between @begdt and @enddt
For reference, we are using Teradata. Teradata can use parameters in Macros, but it won’t allow for strings like the @str variable in the example above. We have access to BTEQ, but in Windows, we can’t create variables like the ones above due to lack of Perl scripting. Any help with this would be greatly appreciated. Thanks in advance
You can do some of this. It isn’t possible to assign a bind variable to a set (So you can’t use it in the in example below).
You can however use global binds. In Toad you specify a bind using :{name}. If you use this Toad will prompt you and ask you for the value of the variable. You can also specify global predefined bind variables using the global bind dialog which is accessible from the top right of the project manager. So to recap you could do part of your example below by executing this.
select sales
from sales_table
where date between :begdt and :enddt
Also if you want to use the SQL editor itself to specify the variables you can either rely on your specific databases support for variables (I unfortunately don’t know much about Teradata so I can’t help you there). Another option is to use Toad scripting which also supports this in a platform independent way.
Binds still wont work but you would be able to write something like this:
– TOAD: SET begdt = ‘2012-01-01’
– TOAD: SET enddt = ‘2013-01-01’
select sales
from sales_table
where date between :begdt and :enddt
For more information on Toad script check out this blog post: http://toad.henrik.org/2009/07/flow-control-for-your-scripts.html
Toad scripting is exactly what we are looking for. The link you provided also has some interesting applications. When I tried to use the Toad script to define the variables, I still get prompted to bind the parameter(s) in the SQL (see screenshot). Teradata's parameter format is :name also, so that might be a problem.
Did you try defining the name var in the Golbal variables prompt.
Mauritz mentions "You can also specify global predefined bind variables using the global bind dialog which is accessible from the top right of the project manager. "
Debbie
This still doesn’t work. It might be because Teradata uses the same format for its parameters. I use the Teradata .Net connection, but I get the same behavior from the ODBC connection as well.
I was having a similar problem with Macros containing parameters using the Teradata .Net connection; maybe it’s related.
