Toad World® Forums

Automation Designer with Variables

Every quarter we have to export to .txt file the same sub-tables from specific tables, like so:
(I'm very new to SQL and Toad, and I have no idea the correct terminology)

select *
from MAIN_TABLE.THIS_OTHER_TABLE
where RANDOM_COLUMN = 'random'

The only thing that changes is MAIN_TABLE, which every quarter has a different sequential number. The rest of the code is the same.

What I want to do is for MAIN_TABLE to be a variable, and not hardcoded, and that I can just change 1 variable in the Automation Designer and it would update all the other queries that I have.

The reason for this is that I need to export another 4 tables that have the exact same query every quarter, with just MAIN_TABLE changing.

Thanks

Probably several ways to do this... here's one way, where you can use the ampersand character to designate a variable, for which you can set a default value, and also get prompted during the run (note that I had to double up on the period for proper parsing) ...

Here's another useful link describing a different way to utilize variables, and probably a little more flexible.. from our lead developer Mr. Dorlon:

2 Likes

This is brilliant!! Thanks for your help!!
I used a combination of your screenshot and what was in the article you referenced.