This tutorial demonstrates how to use Automation to identify and report special circumstances (exceptions). In this tutorial you will use the presence of a NULL value in a column to trigger exception reporting activities.
In this tutorial you will learn:
You will need:
Not all reports are repetitive. You might have a situation in which you want to generate a report only when a set of special circumstances arise. To demonstrate this, assume for this tutorial that you work for a company where orders are entered into a database every day. If the clerk entering the order forgets to submit the order, it is your job to find this mistake (or exception) and report it. An order that was not submitted will have a NULL value in the DATE_SUBMITTED column.
To follow along with this tutorial, create a table that includes a date column named DATE_SUBMITTED. Add some data, and be sure to include a few null values in the DATE_SUBMITTED column.
In the RowCount variable name field, enter a name for this variable. To follow along, enter the name SubmitDate_Exceptions.
The Row Count variable is a built-in variable in Automation. After execution of a query, Toad identifies the number of rows returned and assigns this value to this variable.
In this tutorial, after execution of the query, the Row Count variable value will be equal to the number of rows with a NULL value in the DATE_SUBMITTED column (i.e., the number of orders that were not submitted).
Tip: The Row Count variable can be used in the Select to File, Execute Script, Toad Report, and Toad Pivot Grid activities.
After the script executes, review the Log. There is a message in the Log that specifies the value of the Row Count variable.
Now click the output file hyperlink to view the output file. The number of rows in the output file should match the value of the Row Count Variable.
If, in the previous example, all orders that were entered were also correctly submitted, then the Row Count variable would be assigned a value of 0. So you can see how you can use this variable value to determine when to send an exception report (i.e., when the variable value > 0).
In the design window, click the left branch of the If Condition activity to select it.
Complete the expression to read "#SubmitDate_Exceptions# > 0"
Drag the Send Email activity from the Toolbox to the design window and drop it in the left branch of the If Condition.
With the Send Email activity selected, enter a subject line. Then, in the Body text box, compose a message that includes your Row Count variable, and make sure to surround the variable name with the hash symbol (#) as in the following example.
Today's order entry produced the following number of exceptions: #SubmitDate_Exceptions#. Please see the attached Exception Report.
In this tutorial, you are not using the right branch of the If Condition. Right-click the right branch and select Delete to delete it.
Note: You can add an expression and task to the right branch, if necessary. However, please note that the If Condition is not an IF ELSE statement. Multiple branches can be true. And each branch must have an expression. Also, if you would like to add a branch to the If Condition, right-click it and select Add Branch.
Exception reporting can be very useful, and Automation can make it easy and worry-free.
What is an Expression?
In the If Condition activity, you use an expression to determine if a certain condition or set of conditions exist. But what is an expression?
An expression is a statement (consisting of values, variables, operators, and functions) that returns (or evaluates to) a value. The value can be logical, numerical, or a string.
See Schedule Your Script to learn how to schedule the Automation script.