Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
Functions, 87 of 166
nvl2::=
nvl2
If expr1 is not null, NVL2
returns expr2. If expr1 is null, 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, Oracle 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.
The datatype of the return value is always the same as the datatype of expr2, unless expr2 is character data, in which case the return value's datatype is VARCHAR2
.
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%'; 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
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|