Decode

From Oracle FAQ
Jump to: navigation, search

DECODE is a SQL function that provides similar functionality to an IF-THEN-ELSE or Case statement.

Syntax[edit]

The syntax for the decode function is:

decode(expression, search, result [,search, result]...[,default] )
  • expression is the value to compare
  • search is the value that is compared against expression
  • result is the value returned, if expression is equal to search
  • default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return NULL (no matches found).

Examples[edit]

Decoding code values:

SELECT decode(sex, 'M', 'Male', 'F', 'Female', 'Unknown')
  FROM employees;
SELECT DECODE(day#, 1, 'Monday',
                    2, 'Tuesday',
                    3, 'Wednesday',
                    4, 'Thursday',
                    5, 'Friday',
                    6, 'Saturday',
                    7, 'Sunday',
                    'Invalid day')
  FROM tableX;

Comparing values:

SELECT a, b, decode( abs(a-b), 0, 'a = b',
                              a-b, 'a > b',
                              'a < b')
  FROM tableX;

Aggregating values:

SELECT dept, sum(  decode(sex,'M',1,0)) MALE,
             sum(  decode(sex,'F',1,0)) FEMALE,
             count( decode(sex,'M',1,'F',1) ) TOTAL
  FROM my_emp_table
 GROUP BY dept;

Decode and NULL[edit]

As a rule, comparison to NULL should always return NULL. However, DECODE is an exception as it evaluates NULL == NULL:

SQL> SELECT decode(null,null,1,0) FROM dual;
DECODE(NULL,NULL,1,0)
---------------------
                    1

Also see[edit]