Not all reports are repetitive. Perhaps you have a situation where you only want to produce a report when a set of circumstances arise.
Here is a simple example. Clerks enter orders and at the end of the day submit the batch of orders for processing. If they forgot to submit a batch of orders a manager would want to know this and resolve quickly. The time a batch is submitted is stored in a submitted_date column. A select statement looking for null values in this column would give you one or more rows when this exception occurred.
So how could we automate this query and only send an email to the manager when a clerk did not submit their orders? To accomplish this task we are going to use the Row Count variable and If Condition Activity.
What’s the Row Count Variable?
Any of the activities that execute a select statement have a built in automation variable called Row Count. (That would be these 4 automation activities; Select to File, Execute Script, Toad Report, and Toad Pivot Grid.)
To use this variable just enter a name. When the activity executes your Sql it will set the number of rows returned.
This value can now be used with the If..Condition activity. Add an expression that will define your exception condition. For example, if RC > 0 send email to manager.
Putting the Row Count to Work
Let’s walk through an example. You can follow along with this example if you download this zip file. You need to unzip the files in ‘C:\Temp’ unless you want to change some paths.
1) Start Toad for Data Analysis and create a new Excel connection to the Orders.xls file. (Use File | New Connection. Pick Excel from the group type and browse to the file. Press Select to connect.) I’m using Excel as my database.
2) Next, I need to build my exception query. Open OrdersException.tsm from the sample files. This is a Query builder file where I select from the orders table looking for rows where the submitted_date is null. Run the query to ensure everything is set up correctly. You should get 3 rows.
3) Now let’s build the automation script. (You can open the sample Script_ExceptionReporting.tas or build one yourself following these steps.) Open the automation window and add a ‘Select To File’ activity. Enter RC as the name of the Row Count variable. Browse to the OrdersException.tsm file to set the SQL Script. Choose to export to an Excel file named OrdersNotSubmitted.xls.
4) Select the If..Condition activity from the toolbox. Double click on the first Condition and enter RC>0. (There is an expression builder that can be used for more complicated condition expressions.) Go ahead and delete the right side condition as we aren’t going to use it. To do this, select the condition, right click and delete.
5) Now add the activity you want to perform when the condition is met. An email is the usual activity but for this example I am adding a Log Comment Activity. Here I just want to dereference the variable to the output window so I can confirm the condition is working. In the comment enter ‘Better Send an email. Rows returned were #RC#’. The pound signs ‘#’ dereferences the content of the RC variable.
6) Now we are ready to roll. Click ‘test’ and in the output window you can confirm the number of rows retrieved and the condition result. To view the rows exported click on the file name in the output window. It will open Excel to view the rows.
So there is your automated exception report. Adapt this to your needs. If you have any questions on using this please post them on the Toad for Data Analysts Community.
Have fun!
Debbie