Archives of the TeradataForum

Message Posted: Wed, 15 May 2013 @ 20:43:41 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

That's the way parameters work in SQL Assistant.

If you use the same parameter name in multiple places it will only prompt you to enter it one time.

However you should note that these are not real parameters in the sense generally used in programming. Instead they are substitution strings. This means that if your value is a string you must provide the quotes. So your SQL becomes:

     select *
     from   DBC.TABLES
     where  DatabaseName = 'my_db1'
       and CreatorName = '?target_id'  /* Prompt for user ID */
     union
     select *
     from   DBC.TABLES
     where  DatabaseName = 'my_db2'
       and CreatorName = '?target_id'  /* Use same value from previous prompt */

Also note that if you really only want to have a single parameter in the SQL you can use a WITH prefix to create a temp table and then reference that table in the 2 selects.

As in:

     WITH U (UId) AS (Select '?target_id')
     Select *
     From   DBC.TABLES
     Where  DatabaseName = 'my_db1'
           and CreatorName IN (SELECT UId FROM U)
     ...

(Not recommended as it makes your SQL more complex and not obvious to other readers)

Mike Dempsey

	<Prev	Next>		<<First	<Prev	Next>	Last>>