Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-01 |
|
|
View PDF |
Syntax
Purpose
NVL2
lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1
is not null, then NVL2
returns expr2
. If expr1
is null, then NVL2
returns expr3
.
The argument expr1
can have any datatype. The arguments expr2
and expr3
can have any datatypes except LONG
.
If the datatypes of expr2
and expr3
are different:
If expr2
is character data, then Oracle Database converts expr3
to the datatype of expr2
before comparing them unless expr3
is a null constant. In that case, a datatype conversion is not necessary. Oracle returns VARCHAR2
in the character set of expr2
.
If expr2
is numeric, then Oracle determines which argument has the highest numeric precedence, implicitly converts the other argument to that datatype, and returns that datatype.
See Also: Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion and "Numeric Precedence" for information on numeric precedence |
Examples
The following example shows whether the income of some employees is made up of salary plus commission, or just salary, depending on whether the commission_pct
column of employees
is null or not.
SELECT last_name, salary, NVL2(commission_pct, salary + (salary * commission_pct), salary) income FROM employees WHERE last_name like 'B%' ORDER BY last_name; LAST_NAME SALARY INCOME ------------------------- ---------- ---------- Baer 10000 10000 Baida 2900 2900 Banda 6200 6882 Bates 7300 8468 Bell 4000 4000 Bernstein 9500 11970 Bissot 3300 3300 Bloom 10000 12100 Bull 4100 4100