Decode Function

DECODE Function

DECODE is a very useful function in Oracle queries. And it has the functionality of an IF-THEN-ELSE logic. It is used to display different things based on different values in a column.

The following are the formats for the DECODE function:

DECODE (value, search1, result1 [, search2, result2,] [, search3, result3,]…….[, default])

                                                            OR

DECODE (value, if1, then1 [, if2, then2,]  [, if3, then3,]……..[, else])

                                                            OR

DECODE (value, find1, replace1 [, find2, replace2,]  [find3, replace3,]……[, default])

In the above formats:

value represents is either static value, expression or column in a table / view

search1, search2, search3, …… or if1, if2, if3, …… or find1, find2, find3, …….

is the value that is compared against the value that in the first argument of DECODE.

result1, result2, result3, …. or then1, then2, then3,…. or replace1, replace2, replace3, ……..

is the value returned, if value is equal to search1, search2, search3, ….. or if1, if2, if3,…… or find1, find2, find3,………. respectively.

default or else is optional.  If no matches are found, the DECODE will return the default value.  If default is omitted, then the DECODE function will return NULL.

So, DECODE compares value to each search value one by one. If value is equal to a search, then Oracle Database returns the corresponding result. If no match is found, then Oracle returns default value.  If default is omitted, then Oracle returns NULL.

The following are the examples:

Example1:

This example decodes the value deptno. If deptno is 10, then the function returns 'Sales'; if deptno is 20, then it returns 'Production'; and so forth. If deptno is not 10, 20 or 30 then the function returns 'NULL'.

SELECT ename, sal,

       DECODE (deptno, 10, 'Sales',

                       20, 'Production',

                       30, 'Research') AS Department    FROM emp;

Example2: 

This example decodes the value deptno. If deptno is 10, then the function returns 'Sales'; if deptno is 20, then it returns 'Production'; and so forth. If deptno is not 10, 20 or 30 then the function returns 'Accounting'.

SELECT ename, sal,

       DECODE (deptno, 10, 'Sales',

                       20, 'Production',

                       30, 'Research', ‘Accounting’) AS Department FROM emp;

Example3: 

This example decodes the value deptno. If deptno is 10, then the function returns 'Sales'; if deptno is 20, then it returns 'Production'; and so forth. If deptno is not 10, 20 or 30 then the function returns deptno value.

SELECT ename, sal,

       DECODE (deptno, 10, 'Sales',

                       20, 'Production',

                       30, 'Research', deptno)

  FROM emp;

Example4: 

This example returns the biggest of a given two numbers. If SIGN(&&a-&&b) is 1, then the function returns the value of a; if SIGN(&&a-&&b)is -1, then it returns the value of b. If SIGN(&&a-&&b)is not 1, -1 then the function returns ‘Both are equal’value.

This example returns the biggest of a give two numbers.  If

SELECT DECODE (SIGN(&&a-&&b),

               1, ‘Big is ‘||&a, -1, 'Big is '||&b,

               ‘Both are same')

  FROM dual;

Example5:

This example returns numbers in words (for 1 to 10). If value is 1, then the function returns the value of One; if value is 2, then it returns the value of Two; if value is 3, then it returns the value of Three; and so forth. if value is not 1, 2, 3, 4, 5,……10 then the function returns ‘Sorry, I do not know’value.

SELECT DECODE (&no,1,’One’,2,’Two’,3,’Three’,4,’Four’

               5,’Five’,6,’Six’,7,’Seven’,8,’Eight’,9,’Nine’,10,’Ten’,’Sorry, I do not know’)

FROM dual;

Example6:

This example decodes the value SIGN(sal-2000). If SIGN(sal-2000) is 1, then the function returns 'Salary more than 2000'; if SIGN(sal-2000) is -1, then it returns 'Salary less than 2000'. If SIGN(sal-2000)is not 1, -1 then the function returns ‘Salary is 2000’ value.

SELECT ename, job, sal,

       DECODE (SIGN(sal-2000),

       1, ‘Salary more than 2000’,

      -1, ‘Salary less than 2000’,

       ‘Salary is 2000') “Status”

  FROM emp;

Example7: 

This example decodes the value SIGN(sal-2000). If SIGN(sal-2000) is 1, then the function returns 'Salary more than 2000'; if SIGN(sal-2000) is -1, then it returns 'Salary less than 2000'. If SIGN(sal-2000)is not 1, -1 then the function returns ‘Salary is 2000’ value.

SELECT ename, job, sal, DECODE (SIGN(sal-2000),

                        1, ‘Salary more than 2000’,

                       -1, ‘Salary less than 2000’,

                       ‘Salary is 2000')

  FROM emp;

Example8:

This example decodes the value of job. If job is ‘CLERK’, then the function returns corresponding ename value. If job is not ‘CLERK’ then the function returns NULL value.

SELECT ename, deptno, sal,

       DECODE (LTRIM(RTRIM(UPPER(job))),

       ‘CLERK’, ename) “Clerks”

  FROM emp;

Example9:

This example decodes the value of SIGN ((SELECT MAX(sal) FROM emp)-sal).  If SIGN((SELECT MAX(sal) FROM emp)-sal) is 1, then the function returns corresponding ‘More than Max Salary’ value; If SIGN((SELECT MAX(sal) FROM emp)-sal) is -1, then the function returns corresponding ‘Less than Max Salary’ value; If SIGN((SELECT MAX(sal) FROM emp)-sal) is not 1 or -1 then the function returns ‘Maximum Salary’ value.

SELECT ename, sal,

DECODE (SIGN((SELECT MAX(sal) FROM emp)-sal),

        1, ‘More than Max Salary’

       -1, ‘Less than Max Salary’

         , ‘Maximum Salary’) “Status” FROM emp;