Databases Reference
In-Depth Information
SCOTT
KING
TURNER 1300
ADAMS
JAMES
FORD
MILLER
14 rows selected.
Wait a minute, what happened to the salaries for the other employees? As
noted earlier in the chapter, NULL values provide a great benefit in that they can
indicate that a value is unknown, unavailable, or not applicable. However, when
combined in some kind of calculation with non-NULL values, the result will
always be NULL. For example, adding 15 to an unknown value will result in a
new value that is also unknown.
In the case of the employee salaries and commissions, however, Scott wants to
treat the commissions as zero if they are NULL for the purpose of calculating total
compensation. For this, he will use the NVL function. NVL takes two arguments.
The first argument is compared to NULL, and if it is NULL, it returns the second
argument; otherwise, it returns the first argument. Scott's query can be modified
with the NVL function to produce the correct results:
select ename, sal+nvl(comm,0) from emp;
ENAME SAL+NVL(COMM,0)
---------- ---------------
SMITH 700
ALLEN 1900
WARD 1750
JONES 2975
MARTIN 2650
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1300
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1600
14 rows selected.
