CASE Clause Examples

Consider the following Virtual DataPort view named 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

Examples

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.