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 shows the syntax and logic equivalent for each of the four functions.
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 |
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.
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.
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.
0 comments:
Post a Comment