Decision

A Decision component is a rule-based component that branches the execution flow based on SQL expressions.

A Decision component contains one or more SQL-based rules that determine which branch of a control flow is executed. Each branch is mutually exclusive. Error handling branches are also created by defining appropriate split rules in a Decision component.

A Decision component has a single input port that accepts multiple connections from the Start, Task Group, and Decision components, and two or more output ports each supporting a single connection to a Task Group, Decision, Finish, or Error component.

You can add rules and define the evaluation order using the configuration window. Each rule (condition) is associated with an output port. They are evaluated in the order you specify. You can define a SQL query including functions and variables or call a stored procedure in a rule, as long as it returns a single value for evaluation. The value of a user-defined variable set as an increment variable automatically increments each time the associated branch is executed. By default, when you add a Decision component, a blank rule applies that represents an ELSE condition. If any of specified rules is not true, then the blank rule is executed.

For example, the rule on adding a Decision component by default includes two output branches:

Branch 1 – @project_status = 'SUCCESS'
Branch 2 – no rule exists, this is the ELSE condition (default)

Listed are some examples for the Decision component:

Example 1

@projectstatus = ‘SUCCESS’
This expression is composed of a variable an operator and a literal value.  
The global variable @projectstatus is built-in and available for the user in 
decision expressions.
Valid values for @projectstatus include ‘SUCCESS’ and ‘ERROR’.

Example 2

(SELECT COUNT (*) FROM Customers_Xfrm) >  0
This expression is composed of a select, a comparison operator and a literal value.  
Parentheses around the SELECT are required for IQ.

Example 3

@projectstatus = ‘ERROR’ AND @retryCount = 0
This expression is composed of two variables, two literal values, two comparison operators,
and a logical operator.  The second variable (@retryCount) is an example of a user-defined variable.

Example 4

CustXfrmHasData() > 0 AND @projectStatus = 'SUCCESS'
This expression integrates a user-defined function.

Example 5

CustXfrmSuccess() = 0 AND CustXfrmHasData() > 0
This expression is composed of two user-defined functions.

Decision Component Compatibility with Other SQL Transformation Components
Component	Input	Output
Start	Yes	No
Finish	No	Yes
Error	No	Yes
Task Group	Yes	Yes
Decision	Yes	Yes

To add the Decision component to a SQL Transformation project, drag it from the Component Store onto the Design window.