The CASE function supports two different syntax options, a simple case expression and a searched case expression. It is typically used to control the flow of processing for mutually exclusive conditions. In both versions of the function, each WHEN clause is evaluated in sequence for a True (1) condition. The first True condition encountered initiates an action. If no WHEN clause returns a True condition, no action will be initiated unless an optional OTHERWISE clause is specified. If a True condition is found, the action can consist of one or more functions, including another CASE function.
Category
Specialized
Syntax
CASE <value> WHEN <'constant'> { true_action } [WHEN <'constant'> { true_action }...] OTHERWISE { false_action }
CASE WHEN <condition> { true_action } [WHEN <condition> { true_action }...] OTHERWISE { false_action }
Parameters and Descriptions
| Parameter | Description |
|---|---|
| value | Used in a simple case, the "value" can be the result of any function. |
| 'constant' | Used in a simple case, the 'constant', must be a quoted string and is compared to the "value" parameter to determine if they are equal. |
| condition | Used in a search case, one (1) or more conditions can be tested by the CASE function. Each condition can be any function or combination of functions that returns a true (1) or false (0) value. |
| true_action | One or more actions to be taken if a WHEN is true (1). A true_action is usually one or more other functions and or called Procedures. |
| false_action | One or more actions to be taken if all of the WHEN's are evaluated as false (0). A false_action is usually one or more other functions and or called Procedures. |
Note: It is important to use Boolean functions that return a true (1) or false (0) value from the WHEN within a CASE function. Otherwise, the result will always be false. It is recommended that you keep each keyword, 'constant' or condition and action on separate lines in the command script to improve readability.
Example 1
Control the processing of changed relational table data by checking the name of the table in the changed data record, performing the appropriate mapping procedure and writing the result to the Target datastore.
PROCESS INTO TARGET
SELECT
{
CASE DB2TBLNAME(CDCIN)
WHEN 'EMP_TABLE'
{
CALLPROC(P_MAP_EMP
APPLY (T_EMP)
}
WHEN 'DEPT_TABLE'
{
CALLPROC(P_MAP_DEPT)
APPLY (T_DEPT)
}Example 2
Within an employee datastore, expand the sex code from one (1) character to the full name, using the variable V_TEMP as the temporary placeholder for the full name.
CASE
WHEN SEX = 'M'
V_TEMP = 'MALE'
WHEN SEX = 'F'
V_TEMP = 'FEMALE'
OTHERWISE
V_TEMP = 'OTHER'