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.



Mastering Oracle SQL
Mastering Oracle SQL, 2nd Edition
ISBN: 0596006322
EAN: 2147483647
Year: 2003
Pages: 154

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net