SQL: DECODE Function

The DECODE function can be thought of as an inline IF statement. DECODE takes three or more expressions as arguments. Each expression can be a column, a literal, a function, or even a subquery. Let’s look at a simple example using DECODE:

SELECT lname,DECODE(manager_emp_id, NULL, ‘HEAD HONCHO’, ‘WORKER BEE’) emp_type FROM employee;

LNAME         EMP_TYPE

SMITH            WORKER  BEE

ALLEN            WORKER  BEE

WARD            WORKER  BEE

JONES            WORKER  BEE

MARTIN         WORKER  BEE

BLAKE           WORKER  BEE

CLARK           WORKER  BEE

SCOTT            WORKER  BEE

KING              HEAD  HONCHO

TURNER         WORKER  BEE

ADAMS          WORKER  BEE

JAMES            WORKER  BEE

FORD              WORKER  BEE

MILLER          WORKER  BEE

In this example, the first expression is a column, the second is NULL, and the third and fourth expressions are character literals. The intent is to determine whether each employee has a manager by checking whether an employee’s manager_emp_id column is NULL. The DECODE function in this example compares each row’s manager_emp_id column (the first expression) to NULL (the second expression). If the result of the comparison is true, DECODE returns ‘HEAD HONCHO’ (the third expression); otherwise, ‘WORKER  BEE’ (the last expression) is returned.

Since the DECODE function compares two expressions and returns one of two expressions to the caller, it is important that the expression types are identical or that they can at least be translated to be the same type. This example works because E1 can be compared to E2, and E3 and E4 have the same type. If this were not the case, Oracle would raise an exception, as illustrated by the following example:

SELECT lname,DECODE(manager_emp_id, SYSDATE, ‘HEAD HONCHO’, ‘WORKER BEE’) emp_type FROM employee;
ERROR at line 1:
ORA–00932: inconsistent datatypes: expected DATE got NUMBER

Since the manager_emp_id column, which is numeric, cannot be converted to a DATE type, the Oracle server cannot perform the comparison and must throw an exception. The same exception would be thrown if the two return expressions (E3 and E4) did not have comparable types.

The previous example demonstrates the use of a DECODE function with the minimum number of parameters (four). The next example demonstrates how additional sets of parameters may be utilized for more complex logic:

SELECT p.part_nbr part_nbr, p.name part_name, s.name supplier, DECODE(p.status,‘INSTOCK’, ‘In Stock’,‘DISC’, ‘Discontinued’,‘BACKORD’, ‘Backordered’,‘ENROUTE’, ‘Arriving Shortly’,‘UNAVAIL’, ‘No Shipment Scheduled’, ‘Unknown’) part_statusFROM part p INNER JOIN supplier sON p.supplier_id = s.supplier_id;

PART_NBR   PART_NAME                   SUPPLIER              PART_STATUS

AI5-4557         Acme  Part  AI5-4557     Acme  Industries         In  Stock

TZ50828         Tilton  Part  TZ50828       Tilton  Enterprises       In  Stock

EI-T5-001        Eastern  Part  EI-T5-001 Eastern  Importers      In  Stock

This example compares the value of a part’s status column to each of five values, and, if a match is found, returns the corresponding string. If a match is not found, then the string  ‘Unknown’ is returned. Although the 12 parameters in this example are a great deal more than the 4 parameters of the earlier example, we are still a long way from the maximum allowable parameters, which is 255.