Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

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

CASE control_expression simple_when_list

[ else_clause ]

END

 

CASE searched_when_list

[ else_clause ]

END

 

simple_when :

WHEN expression THEN result

 

searched_when :

WHEN search_condition THEN result

 

else_clause :

ELSE result

 

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

SELECT a,

       CASE WHEN a=1 THEN 1

            WHEN a=2 THEN 1.2345

            ELSE 1.234567890

       END

FROM case_tbl;

            a  case when a=1 then 1 when a=2 then 1.2345 else 1.234567890 end

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

            1  1.000000000

            2  1.234500000

            3  1.234567890

         NULL  1.234567890

 

--an error occurs when result types are not convertible

SELECT a,

       CASE WHEN a=1 THEN 'one'

            WHEN a=2 THEN 'two'

            ELSE 1.2345

       END

FROM case_tbl;

ERROR: Cannot coerce 'one' to type double.