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