Decode Function in Oracle

Decode helps us to use conditional inquires by doing the work of a case or if-then-else statement (switch or if-then-else).  The syntax is:

Decode(col/expression,search1,result1 [,search2,result2] [,search3,result3] [,search4,result4] […………………..] [, default])

SQL> select ename, empno, sal, job,

decode (job,'MANAGER', sal+5000) new_sal from emp;

ENAME          EMPNO       SAL JOB         NEW_SAL

---------- --------- --------- --------- ---------

SMITH           7369       800 CLERK

ALLEN           7499      1600 SALESMAN

WARD            7521      1250 SALESMAN

JONES           7566      2975 MANAGER        7975

MARTIN          7654      1250 SALESMAN

BLAKE           7698      2850 MANAGER        7850

CLARK           7782      2450 MANAGER        7450

SCOTT           7788      3000 ANALYST

KING            7839      5000 PRESIDENT

TURNER          7844      1500 SALESMAN

ADAMS           7876      1100 CLERK

JAMES           7900       950 CLERK

FORD            7902      3000 ANALYST

MILLER          7934      1300 CLERK

14 rows selected.

SQL> select ename, empno, sal,

decode (job,'MANAGER',SAL+5000) "N_S", job from emp;

ENAME          EMPNO       SAL       N_S JOB

---------- --------- --------- --------- ---------

SMITH           7369       800           CLERK

ALLEN           7499      1600           SALESMAN

WARD            7521      1250           SALESMAN

JONES           7566      2975      7975 MANAGER

MARTIN          7654      1250           SALESMAN

BLAKE           7698      2850      7850 MANAGER

CLARK           7782      2450      7450 MANAGER

SCOTT           7788      3000           ANALYST

KING            7839      5000           PRESIDENT

TURNER          7844      1500           SALESMAN

ADAMS           7876      1100           CLERK

JAMES           7900       950           CLERK

FORD            7902      3000           ANALYST

MILLER          7934      1300           CLERK

14 rows selected.

SQL> select job, sal, decode (job,'ANALYST',sal*1.10, 'CLERK',SAL*1.15, 'MANAGER',sal*1.20,sal) "rev_sal" from emp ORDER BY JOB;

JOB             SAL   rev_sal

--------- --------- ---------

ANALYST        3000      3300

ANALYST        3000      3300

CLERK           800       920

CLERK          1100      1265

CLERK          1300      1495

CLERK           950    1092.5

MANAGER        2975      3570

MANAGER        2450      2940

MANAGER        2850      3420

PRESIDENT      5000      5000

SALESMAN       1600      1600

SALESMAN       1250      1250

SALESMAN       1500      1500

SALESMAN       1250      1250

14 rows selected.

SQL> select ename,sal,deptno,decode(deptno,10,

 decode(job,'MANAGER',sal+4000,sal),sal+1000) "new_sal" from emp;

ENAME            SAL    DEPTNO   new_sal

---------- --------- --------- ---------

SMITH            800        20      1800

ALLEN           1600        30      2600

WARD            1250        30      2250

JONES           2975        20      3975

MARTIN          1250        30      2250

BLAKE           2850        30      3850

CLARK           2450        10      6450

SCOTT           3000        20      4000

KING            5000        10      5000

TURNER          1500        30      2500

ADAMS           1100        20      2100

JAMES            950        30      1950

FORD            3000        20      4000

MILLER          1300        10      1300

14 rows selected.