CASE

Description

The CASE expression uses the SQL statement to perform an IF ... THEN statement. When a result of comparison expression specified in a WHEN clause is true, a value specified in THEN value is returned. A value specified in an ELSE clause is returned otherwise. If no ELSE clause exists, NULL is returned.

Syntax

CASEcontrol_expression simple_when_list

[ else_clause ]

END

CASEsearched_when_list

[ else_clause ]

END

simple_when :

WHENexpressionTHENresult

searched_when :

WHENsearch_conditionTHENresult

else_clause :

ELSEresult

result :

expression | NULL

The CASE expression must end with the END keyword. A control_expression argument and an expression argument in simple_when expression should be comparable data types. The data types of result specified in the THEN ... ELSE statement should all same, or they can be convertible to common data type.

The data type for a value returned by the CASE expression is determined based on the following rules.

If data types for result specified in the THEN statement are all same, a value with the data type is returned.

If data types can be convertible to common data type even though they are not all same, a value with the data type is returned.

If any of values for result is a variable length string, a value data type is a variable length string. If values for result are all a fixed length string, the longest character string or bit string is returned.

If any of values for result is an approximate numeric data type, a value with a numeric data type is returned. The number of digits after the decimal point is determined to display all significant figures.

Example

--creating a table

CREATE TABLE case_tbl( a INT);

INSERT INTO case_tbl VALUES (1);

INSERT INTO case_tbl VALUES (2);

INSERT INTO case_tbl VALUES (3);

INSERT INTO case_tbl VALUES (NULL);

--case operation with a search when clause

SELECT a,

CASE WHEN a=1 THEN 'one'

WHEN a=2 THEN 'two'

ELSE 'other'

END

FROM case_tbl;

a case when a=1 then 'one' when a=2 then 'two' else 'other' end

===================================

1 'one'

2 'two'

3 'other'

NULL 'other'

--case operation with a simple when clause

SELECT a,

CASE a WHEN 1 THEN 'one'

WHEN 2 THEN 'two'

ELSE 'other'

END

FROM case_tbl;

a case a when 1 then 'one' when 2 then 'two' else 'other' end

===================================

1 'one'

2 'two'

3 'other'

NULL 'other'

--result types are converted to a single type containing all of significant figures