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.

USER MANUALS

CASE Clause Examples¶