Oracle Conversion Function - Oracle/PLSQL DECODE Function

This Oracle tutorial explains how to use the Oracle/PLSQL DECODE function.

The Oracle/PLSQL DECODE function works as an IF-THEN-ELSE statement.

The format:

decode( expression , search , result [, search2 , result2]... [, default] )

It can be mapped to:


if expression = search
   return result
else if expression = search2
   return result2
...
else if expression = searchN
   return resultN
else 
   return default;

For example


SQL> select decode(1,1,'One',
  2                  2,'Two',
  3                  'Unknown') from dual;

DEC
---
One

SQL>

The maximum number of parameters in a DECODE function is 255. This includes the expression, search, and result arguments.

Syntax

The syntax for the Oracle/PLSQL DECODE function is:

DECODE( expression , search , result [, search2 , result2]... [, 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. It is like the final else statement in a normal if statement. If default is omitted, then the DECODE function will return null if no matches are found.

Example

Using the decode function to translate employee numbers:


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
-- from w  w w. j a  v a2 s . c o m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

SQL> select empno, decode(empno, 1, 'One',
  2                              2,'Two',
  3                              'Other')
  4  from emp;

     EMPNO DECOD
---------- -----
         1 One
         2 Two
         3 Other
         4 Other
         5 Other
         6 Other
         7 Other
         8 Other
         9 Other
        10 Other
        11 Other

11 rows selected.

SQL>
SQL>
SQL>
SQL>

Example 2

The following code shows how to compare dates in DECODE function, if date1 > date2, return date2. Otherwise, the DECODE function should return date1.

DECODE((date1 - date2) - ABS(date1 - date2), 0, date2, date1)

We can combine SIGN and DECODE functions to compare numeric values.

DECODE(SIGN(actual-target), -1, 'less than', 
                             0, 'equal', 
                             1, 'greater than')