Home Page for the TeradataForum
 
https:

Archives of the TeradataForum

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


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


Subj:   Re: Can I use variables in SQL Assistant?
 
From:   Dempsey, Mike

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 24 Jul 2020