Toad Data Point Automation Series : Blog #12 – Variables

In Toad for Data Analysts 2.5 Automation Variables were added which greatly expanded what you could accomplish in an automation script. Variables can be used in almost every activity.

What is a variable?

 A variable is a symbol or name that stands for a value. Variables play an important part in scripts. Rather than entering data directly into the script you can use variables. When the script is executed the variables are replaced with real values. In this way, variables can be used to represent and store data that may be different each time the task is run. 

In a SQL statement you can designate a variable by putting a colon in front of the variable name. When executing the statement, Toad Data Point will prompt you for the value.

Using variables

To add a variable to an Automation script, click or drag the “Set Variable” system activity onto the design surface. Enter a name for the variable and initial value. In my example I will use the variable name Day. I could enter a value here but that value would not change and would not have much use. Instead I am going to use the SQL option and enter a query that will give me the day of the week.

Dereferencing Variables

If I want to use the content of variable I must always surround the variable name with ‘#’ signs. Let me demonstrate this by using the comment activity. I can enter the comment “The value of Day = #Day#”. Now when I run the script I can see that the value of Day is Monday.

Using in Report Names

So what can I do with this variable? For one thing I can use this variable in the name of my report. So let’s add a Select to File activity and enter a report SQL. When choosing a report name I can include the variable content. So my file name is Report_#Day#.xlsx. At run time the variable will be replaced with the correct content of the variable. IE: Report_Monday.xlsx.

Using Variables in SQL

You can also use the variable to filter your SQL results. To do so use the colon sign and then the variable name. A column sign is used here because this is the correct symbol for binding the variable to send to the database.

Email

Now to complete this use case we can use the variable content in our subject of an email as well as the body. Also, when we use the attachment editor we can see our filename that uses the variable and add this to our email.

Variables can make your scripts dynamic and extensible.