Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
|
|
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 precedenceExamples
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