USER MANUALS

CASE Expressions

The CASE clause provides an if-then-else type of logic.

CASE syntax
CASE <value:expression> WHEN <compare_value:expression>
     THEN result [ WHEN <compare_value:expression> THEN result ...]
     [ ELSE result ]
END

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

<condition> ::=
    <condition> AND <condition>
  | <condition> OR <condition>
  | NOT <condition>
  | ( <condition> )
  | <value> <binary operator> <value> [ , <value> ]*
  | <value> <binary operator> ( <value> [ , <value> ]* )
  | <value> BETWEEN <value> AND <value>
  | <value> <unary operator>

The CASE clause can be used in two different ways:

  1. CASE evaluates an expression and obtains a value. Then, it compares that value with the expression of every WHEN clause. When it founds a match, returns the result value.

  2. CASE evaluates the condition of every WHEN clause until it founds a match. When it does, returns the result value.

In both cases, if there is no ELSE clause and there is not any matching condition, CASE returns NULL.

All the result expressions must have a compatible type. So, for instance, it is not possible that one result has type boolean and other, integer. But it is possible that one result has type integer and the other float.

CASE Clause Examples

Let us say you have a view called internet_inc:

id | summary

ttime

taxid

1

Error in ADSL router

2005-06-29 19:19:41.0

B78596011

2

Incident in ADSL router

2005-06-29 19:19:41.0

B78596012

3

Install additional line

2005-06-29 19:19:41.0

B78596013

4

Bandwidth increase

2005-06-29 19:19:41.0

B78596014

Example 1

SELECT id
    , summary
    , CASE
        WHEN LEN(summary) > 22
            THEN summary
        ELSE id
        END
FROM internet_inc

id

summary

case

1

Error in ADSL router

1

2

Incident in ADSL router

Incident in ADSL router

3

Install additional line

Install additional line

4

Bandwidth increase

4

Example 2

SELECT id
    , CASE
        WHEN id = 1 THEN true
        ELSE id
    END AS is_first
FROM internet_inc

Error executing sentence: Incorrect select sentence: CASE argument
IINC_ID is not compatible with the rest of values.

The type of the result of the WHEN clause is incompatible with the one of the ELSE clause. The first one has type boolean and the other, long.

Example 3

SELECT id
    ,CASE
        WHEN id = 1
            THEN 'first'
        ELSE id
        END AS is_first
FROM internet_inc

id

is_first

1

first

2

2

3

3

4

4

Note

If the type of the results of the WHEN or ELSE clauses are not the same, they are automatically converted to obtain a valid result. In this case the results are converted to String.

Example 4

The CASE clause can also be used in the WHERE part of a query.

SELECT *
FROM internet_inc
WHERE true = (
        CASE id
            WHEN 1
                THEN true
            ELSE false
            END
        )

id

summary

ttime

taxid

1

Error in ADSL router

2005-06-29 19:19:41.0

B78596011

Example 5

These two queries are equivalent and obtain the same result, but use CASE in different ways:

SELECT id
    , CASE id
        WHEN CASE id
                WHEN 1
                    THEN 1
                ELSE 2
                END
            THEN 'first'
        WHEN 2
            THEN 'second'
        ELSE 'other'
        END
FROM internet_inc;

SELECT id
    , CASE id
        WHEN CASE
                WHEN id = 1
                    THEN 1
                ELSE 2
                END
            THEN 'first'
        WHEN 2
            THEN 'second'
        ELSE 'other'
        END
FROM internet_inc;

id

case

1

first

2

first

3

other

4

other

Note

CASE returns the result of the first WHEN clause that evaluates to true. In this example, the first and second WHEN conditions are true, but it returns the result of the first one.

Add feedback