Using DECODE to Make Decisions

Using DECODE to Make Decisions :

The most common usage of the DECODE function is to make decisions about a data value. In its basic form, the DECODE statement is like an IF-THEN-ELSE programming construct. Below is an example to show how DECODE can make decisions. In the SCOTT.DEPT table, the LOC column determines the department location. The information needed is which region that department falls in. Use the DECODE function to determine the regions:

SQL> SELECT loc,DECODE(loc,'NEW YORK','EASTERN','BOSTON','EASTERN',
  2  'DALLAS','CENTRAL','LOS ANGELES','WESTERN','UNKNOWN') AS region
  3  FROM dept;
LOC           REGION
------------- -------
NEW YORK      EASTERN
DALLAS        CENTRAL
CHICAGO       UNKNOWN
BOSTON        EASTERN

The LOC column is passed to the DECODE function. If the value of the LOC column is 'NEW YORK', then the department is in the EASTERN region, similarly for BOSTON. DALLAS is in the CENTRAL region and LOS ANGELES is in the WESTERN region. If the LOC column contains a value different than the ones enumerated in the DECODE function, then UNKNOWN will be returned.

A common trick with the DECODE function is to determine if the a column's value is above or below a static value. The trick with this operation is to use the SIGN function in the DECODE function. For instance, in the SCOTT.EMP table, anyone whose salary is higher than $3,000 is deemed to be a manager. Those whose salary is $3,000 and below are non-managers. Using the DECODE function, it is possible to quickly determine those employees who are managers or not.

SQL> SELECT empno,ename,sal,
  2  DECODE(SIGN(sal-3000),1,'MANAGER','NON-MANAGER') AS emp_type
  3  FROM emp;
     EMPNO ENAME             SAL EMP_TYPE
---------- ---------- ---------- -----------
      7369 SMITH             800 NON-MANAGER
      7499 ALLEN            1600 NON-MANAGER
      7521 WARD             1250 NON-MANAGER
      7566 JONES            2975 NON-MANAGER
      7654 MARTIN           1250 NON-MANAGER
      7698 BLAKE            2850 NON-MANAGER
      7782 CLARK            2450 NON-MANAGER
      7788 SCOTT            3000 NON-MANAGER
      7839 KING             5000 MANAGER
      7844 TURNER           1500 NON-MANAGER
      7876 ADAMS            1100 NON-MANAGER


The trick with the sign function is that when you take the SAL-3000 calculation, it will return a positive number of the salary is larger than $3,000. The SIGN function will return a '1' if the calculation is positive. If the difference is positive, then the employee is a manager. If the result is not positive, then they are not a manager. The DECODE function figures this out nicely for us.