Team LiB   Previous Section   Next Section

9.1 DECODE, 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, 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 an SQL statement without resorting to a procedural language like PL/SQL. Table 9-1 shows the syntax and logic equivalent for each of the three 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

NVL(E1, E2)

IF E1 IS NULL THEN E2 ELSE E1

NVL2(E1, E2, E3)

IF E1 IS NULL THEN E3 ELSE E2

The following two sections go into detail about the functions listed in Table 9-1.

9.1.1 DECODE

The DECODE function can be thought of as an inline IF statement. DECODE takes four 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, 'MANAGER', 'NON-MANAGER') emp_type
FROM employee;

LNAME                EMP_TYPE
-------------------- -----------
Brown                MANAGER
Smith                MANAGER
Blake                MANAGER
Freeman              NON-MANAGER
Grossman             NON-MANAGER
Thomas               NON-MANAGER
Powers               NON-MANAGER
Jones                NON-MANAGER
Levitz               NON-MANAGER
Boorman              NON-MANAGER
Fletcher             NON-MANAGER
Dunn                 NON-MANAGER
Evans                NON-MANAGER
Walters              NON-MANAGER
Young                NON-MANAGER
Houseman             NON-MANAGER
McGowan              NON-MANAGER
Isaacs               NON-MANAGER
Jacobs               NON-MANAGER
King                 NON-MANAGER
Fox                  NON-MANAGER
Anderson             NON-MANAGER
Nichols              NON-MANAGER
Iverson              NON-MANAGER
Peters               NON-MANAGER
Russell              NON-MANAGER

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 is 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 'MANAGER' (the third expression), otherwise 'NON-MANAGER' (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, 'MANAGER', 'NON-MANAGER') emp_type
FROM employee;

ERROR at line 1:
ORA-00932: inconsistent datatypes

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, supplier s
WHERE p.supplier_id = s.supplier_id;

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.

9.1.2 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 the previous section: 

SELECT lname,
  NVL2(manager_emp_id, 'NON-MANAGER', 'MANAGER') emp_type
FROM employee;

LNAME                EMP_TYPE
-------------------- -----------
Brown                MANAGER
Smith                MANAGER
Blake                MANAGER
Freeman              NON-MANAGER
Grossman             NON-MANAGER
Thomas               NON-MANAGER
Powers               NON-MANAGER
Jones                NON-MANAGER
Levitz               NON-MANAGER
Boorman              NON-MANAGER
Fletcher             NON-MANAGER
Dunn                 NON-MANAGER
Evans                NON-MANAGER
Walters              NON-MANAGER
Young                NON-MANAGER
Houseman             NON-MANAGER
McGowan              NON-MANAGER
Isaacs               NON-MANAGER
Jacobs               NON-MANAGER
King                 NON-MANAGER
Fox                  NON-MANAGER
Anderson             NON-MANAGER
Nichols              NON-MANAGER
Iverson              NON-MANAGER
Peters               NON-MANAGER
Russell              NON-MANAGER

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, employee mgr
WHERE emp.manager_emp_id = mgr.emp_id (+);

EMPLOYEE             MANAG
-------------------- -----
Brown                NONE
Smith                NONE
Blake                NONE
Freeman              Blake
Grossman             Blake
Thomas               Blake
Powers               Blake
Jones                Blake
Levitz               Blake
Boorman              Blake
Fletcher             Blake
Dunn                 Blake
Evans                Blake
Walters              Blake
Young                Blake
Houseman             Blake
McGowan              Blake
Isaacs               Blake
Jacobs               Blake
King                 Blake
Fox                  King
Anderson             King
Nichols              King
Iverson              King
Peters               King
Russell              King

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.

    Team LiB   Previous Section   Next Section