Conditional Expressions
Expressions are building blocks for creating conditional expressions that convert a value from one form to another. Expressions include:
·
CASE
(Switch)
·
CASE
(If)
CASE (Switch)
This form of the
CASE statement is also referred to as the CASE(Lookup) form. The value of expr1 is examined, then the WHEN expressions. If expr1 matches any WHEN expression, it assigns the value in the
corresponding THEN expression.If none of the
WHEN expressions match, it assigns the default
value specified in the ELSE expression. If no ELSE expression is specified, the system automatically adds an ELSE NULL.If expr1 matches an expression in multiple
WHEN clauses, only the expression following the first
match is assigned.CASE expr1
WHEN expr2 THEN expr3
{WHEN expr... THEN expr...}
ELSE expr
END
Where:
CASE starts the CASE statement. Must be followed by an expression and
one or more WHEN and THEN statements, an optional ELSE statement, and the END keyword.WHEN specifies the condition to be satisfied.THEN specifies the value to assign if the
corresponding WHEN expression is
satisfied.ELSE specifies the value to assign if none of
the WHEN conditions are satisfied. If omitted, ELSE NULL is assumed.END ends the CASE statement.CASE "TableHeading"."Column Name"
WHEN 'Col_Val1' THEN 'Val1'
WHEN 'Col_Val2' THEN 'Val2'
WHEN 'Col_Val3' THEN 'Val3'
ELSE "TableHeading"."Column Name"
END
In this example, the
WHEN statements must reflect a strict
equality.
CASE (If)
This form of the
CASE statement evaluates each WHEN condition and if satisfied, assigns the
value in the corresponding THEN expression.If none of the
WHEN conditions are satisfied, it assigns the
default value specified in the ELSE expression. If no ELSE expression is specified, the system automatically adds an ELSE NULL.CASE
WHEN request_condition1 THEN expr1
{WHEN request_condition2 THEN expr2}
{WHEN request_condition... THEN expr...}
ELSE expr
END
Where:
CASE starts the CASE statement. Must be followed by one or more WHEN and THEN statements, an optional ELSE statement, and the END keyword.WHEN specifies the condition to be satisfied.THEN specifies the value to assign if the
corresponding WHEN expression is
satisfied.ELSE specifies the value to assign if none of
the WHEN conditions are satisfied. If omitted, ELSE NULL is assumed.END ends the CASE statement.CASE
WHEN ("TableHeading"."ColumnName1"='Val_A' AND "TableHeading"."ColumnName"='Value1') THEN 'Val1'
WHEN ("TableHeading"."ColumnName2"='Val_B' AND "TableHeading1"."ColumnName2"='Value2') THEN 'Val2'
WHEN "TableHeading"."Column Name"='Value3' THEN 'Val3'
ELSE "TableHeading"."Column Name"
END
Note :
Unlike the case-switch form, the WHEN statements in the case-if form allow comparison operators in a CASE statement, AND has precedence over OR.
