Case Expressions

Case expressions allow you to add conditional logic by using either a simple-case or searched-case structure.

Syntax

case_expr ::= simple_case_expression | searched_case_expression
simple_case_expression ::= CASE input_expression 
                            (WHEN when_expression THEN result_expression )...
                            [ ELSE else_result_expression ] 
                           END

searched_case_expression ::= CASE 
                             ( WHEN boolean_expression THEN result_expression ) ...
                             [ ELSE else_result_expression ] 
                           END
input_expression ::= expression
when_expression ::= expression
result_expression ::= value_returned_expression
else_result_expression ::= value_returned_expression
boolean_expression ::= boolean_returned_expression

• Use a simple CASE expression when you want to evaluate a single expression against multiple potential values.
• Use a searched CASE expression when you need to evaluate multiple, independent boolean expressions to determine the result.

Example 1: Simple Case Expression

CASE CUSTOMERS.CUST_VALID 
                        WHEN 'I' THEN 'Inactive' WHEN 'A' THEN 'Active' 
                        ELSE 'Unknown' 
END

In this example, if the CUST_VALID column has a value of I, it returns Inactive. If the value is A, it returns Active. If neither condition is met, it returns Unknown.

Example 2: Searched Case Expression

CASE 
     WHEN CUSTOMERS.CUST_YEAR_OF_BIRTH <= 1973 THEN 'Segment A' 
     WHEN CUSTOMERS.CUST_YEAR_OF_BIRTH >1973 THEN 'Segment B' 
END

In this example, if CUST_YEAR_OF_BIRTH is less than or equal to 1973, it assigns Segment A. Otherwise, it assigns Segment B.

CASE WHEN ISNULL(CUSTOMERS.CUST_VALID) THEN 'Unknown' ELSE 'Known' END

In this example, if CUST_VALID is NULL, it returns Unknown. Otherwise, it returns Known.