Variables in sql statements

Toad Data Point & Toad Intelligence Central
1

Hello all,

Hope you can all help. I have a query that is approx. 110 statements long. I have a column that we use to filter data in each of these statements called “rpt_mth” this is a char(7) field in the format of ‘yyyy-mm’. This is called about 40+ times. As it is now the users have to find each area and change it for each run. This puts the potential of making mistakes. I have used the prompts function to call it once. However with dealing with other filters you can’t control the way a user enters the data. What I would like to do is set a variable at the begining of the statement strings at the top and declare the rpt_mth. This way the user can see what was there before and adjust one value and that value be used in all of the below statements. Can this be done? If so can you send me an example.

Thank,
Jeff

2

The only way I know of doing that is to use an Automation script. Here you would need to define the variable once and then it would be used by all activities that had SQL that used the same named var. See attached script as example. It is using my connections and SQL so will not run but you can see the mechanics of how it works.

Debbie
Script_1_Version3.1 (10.7 KB)