9.1 DECODE, NULLIF, NVL, and NVL2

Most of Oracle's built-in functions are designed to solve a specific problem. If you need to find the last day of the month containing a particular date, for example, the LAST_DAY function is just the ticket. The DECODE, NULLIF, NVL, and NVL2 functions, however, do not solve a specific problem; rather, they are best described as inline if-then-else statements. These functions are used to make decisions based on data values within a SQL statement without resorting to a procedural language like PL/SQL. Table 9-1 shows the syntax and logic equivalent for each of the four functions.

Table 9-1. If-then-else function logic

Function syntax

Logic equivalent

DECODE(E1, E2, E3, E4)

IF E1 = E2 THEN E3 ELSE E4

NULLIF(E1, E2)

IF E1 = E2 THEN NULL ELSE E1

NVL(E1, E2)

IF E1 IS NULL THEN E2 ELSE E1

NVL2(E1, E2, E3)

IF E1 IS NULL THEN E3 ELSE E2


9.1.1 DECODE

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_status

FROM part p INNER JOIN supplier s

ON 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.

9.1.2 NULLIF

The NULLIF function compares two expressions and returns NULL if the expressions are equivalent, or the first expression otherwise. The equivalent logic using DECODE looks as follows:

DECODE(E1, E2, NULL, E1)

NULLIF is useful if you want to substitute NULL for a column's value, as demonstrated by the next query, which shows salary information for only those employees making less than $2000:

SELECT fname, lname, 

  NULLIF(salary, GREATEST(2000, salary)) salary

FROM employee;



FNAME                LNAME                    SALARY

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

JOHN                 SMITH                       800

KEVIN                ALLEN                      1600

CYNTHIA              WARD                       1250

TERRY                JONES

KENNETH              MARTIN                     1250

MARION               BLAKE

CAROL                CLARK

DONALD               SCOTT

FRANCIS              KING

MARY                 TURNER                     1500

DIANE                ADAMS                      1100

FRED                 JAMES                       950

JENNIFER             FORD

BARBARA              MILLER                     1300

In this example, the GREATEST function returns either the employee's salary or 2000, whichever is greater. The NULLIF function compares this value to the employee's salary and returns NULL if they are the same.

9.1.3 NVL and NVL2

The NVL and NVL2 functions allow you to test an expression to see whether it is NULL. If an expression is NULL, you can return an alternate, non-NULL value, to use in its place. Since any of the expressions in a DECODE statement can be NULL, the NVL and NVL2 functions are actually specialized versions of DECODE. The following example uses NVL2 to produce the same results as the DECODE example shown in a previous section:

SELECT lname,

  NVL2(manager_emp_id, 'WORKER BEE', 'HEAD HONCHO') 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

NVL2 looks at the first expression, manager_emp_id in this case. If that expression evaluates to NULL, NVL2 returns the third expression. If the first expression is not NULL, NVL2 returns the second expression. Use NVL2 when you wish to specify alternate values to be returned for the case when an expression is NULL, and also for the case when an expression is not NULL.

The NVL function is most commonly used to substitute a default value when a column is NULL. Otherwise, the column value itself is returned. The next example shows the ID of each employee's manager, but substitutes the word 'NONE' when no manager has been assigned (i.e., when manager_emp_id is NULL):

SELECT emp.lname employee, 

  NVL(mgr.lname, 'NONE') manager

FROM employee emp LEFT OUTER JOIN employee mgr

ON emp.manager_emp_id = mgr.emp_id;



EMPLOYEE             MANAGER

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

FORD                 JONES

SCOTT                JONES

JAMES                BLAKE

TURNER               BLAKE

MARTIN               BLAKE

WARD                 BLAKE

ALLEN                BLAKE

MILLER               CLARK

ADAMS                SCOTT

CLARK                KING

BLAKE                KING

JONES                KING

SMITH                FORD

KING                 NONE

Even though DECODE may be substituted for any NVL or NVL2 function, most people prefer to use NVL or NVL2 when checking to see if an expresssion is NULL, presumably because the intent is clearer. Hopefully, the next section will convince you to use CASE expressions whenever you are in need of if-then-else functionality. Then you won't need to worry about which built-in function to use.