Open topic with navigation

TIBCO ActiveSpaces® 4.8.0

# CASE Expressions

A CASE expression is used like an `if-then-else` construct to conditionally return a value. There are two forms of syntax for CASE expression, the simple form and the searched form. The simple form is used to test a single operand for equality against multiple expressions. The searched form of a CASE expression is more flexible and allows for testing multiple conditions.

For more information about using CASE expressions in the select list of SELECT statement, see The Select List.

## Simple CASE Expression

The simple CASE expression implies equality (=) is used for comparisons. One `<common_operand>` is tested against multiple values. It is frequently used to transform one set of values to another longer form.

Syntax:

    CASE <common_operand>
        WHEN <expression> THEN <result>
       [WHEN <expression> THEN <result>
        . . .]
            [ELSE <result>]
    END

For example:

    SELECT lastname,
        CASE gender
            WHEN ‘M’ THEN ‘Male’
            WHEN ‘m’ THEN ‘Male’
            WHEN ‘0’ THEN ‘Male’
            WHEN ‘F’ THEN ‘Female’
            WHEN ‘f’ THEN ‘Female’
            WHEN ‘1’ THEN ‘Female’
            ELSE ‘Unknown’
        END
    FROM employees

Equality expressions cannot be used to test for NULL so you cannot use a simple CASE expression to test for NULL. You must use a searched CASE expression with IS NULL or IS NOT NULL when testing for NULL.

## Searched CASE Expression

The searched CASE expression is good to use when you want to work with a greater range of tests. Any boolean expression qualifies as a WHEN `<expression>`.

Syntax:

    CASE
        WHEN <condition> THEN <result>
       [WHEN <condition> THEN <result>
        . . .]
            [ELSE <result>]
    END

The result of the first `true` boolean expression is returned. For example, the following SELECT statement uses a searched CASE expression to prevent division by zero:

    SELECT ProductID, Name, ProductNumber, Cost, ListPrice,
    CASE
        WHEN ListPrice = 0 THEN NULL
        ELSE Cost / ListPrice
    END AS CostOfGoodSold
    FROM Product

The searched CASE expression can also be used to test a result column for NULL whereas a simple CASE expression cannot. The following simple CASE expression is invalid:

    SELECT
    CASE middle_name
        WHEN NULL THEN ‘<NULL>’
        ELSE
            middle_name
    END

In SQL you cannot use equality to test if a column is NULL (empty). Instead you must use 'IS NULL' as follows:

    SELECT
    CASE
        WHEN middle_name IS NULL THEN ‘<NULL>’
        ELSE
            middle_name
    END

## CASE Comparisons

When you use a simple CASE expression or a searched CASE expression, the following behavior of comparisons is true:

- WHEN clauses are evaluated in the order they are defined.
- When an ELSE clause is provided and none of the previous WHEN clause evaluations match, the ELSE result is returned.
- If an ELSE clause is omitted and none of the previous WHEN clause evaluations match, NULL (SQL NULL) is returned.

Copyright © 2009-2022. Cloud Software Group, Inc. All Rights Reserved.