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 | 

DECODE Function

Description

As well as a CASE expression, the DECODE function performs the same functionality as the IF ... THEN ... ELSE statement. It compares the expression argument with search argument, and returns the result corresponding to search that has the same value. It returns default if there is no search with the same value, and returns NULL if default is omitted. An expression argument and a search argument to be comparable should be same or convertible each other. The number of digits after the decimal point is determined to display all significant figures including valid number of all result.

Syntax

DECODE( expression, search, result [, search, result]* [, default] )

 

result :

result | default | NULL

DECODE(a, b, c, d, e, f) has the same meaning as the CASE statement below.

CASE WHEN a = b THEN c

WHEN a = d THEN e

ELSE f

END

Example

SELECT * FROM case_tbl;

            a

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

            1

            2

            3

         NULL

 

--Using DECODE function to compare expression and search values one by one

SELECT a, DECODE(a, 1, 'one', 2, 'two', 'other') FROM case_tbl;

            a  decode(a, 1, 'one', 2, 'two', 'other')

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

            1  'one'

            2  'two'

            3  'other'

         NULL  'other'

 

 

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

SELECT a, DECODE(a, 1, 1, 2, 1.2345, 1.234567890) FROM case_tbl;

            a  decode(a, 1, 1, 2, 1.2345, 1.234567890)

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

            1  1.000000000

            2  1.234500000

            3  1.234567890

         NULL  1.234567890

 

--an error occurs when result types are not convertible

SELECT a, DECODE(a, 1, 'one', 2, 'two', 1.2345) FROM case_tbl;

 

ERROR: Cannot coerce 'one' to type double.