These
functions work with any data type and pertain to the use of null values in the
expression list.
•
NVL
(expr1, expr2)
•
NVL2
(expr1, expr2, expr3)
•
NULLIF
(expr1, expr2)
•
COALESCE
(expr1, expr2, ..., exprn)
NVL
Function
To convert
a null value to an actual value, use the NVL function.
•
Data
types must match:
•
NVL(commission_pct,0)
•
NVL(hire_date,'01-JAN-97')
•
NVL(job_id,'No
Job Yet')
Syntax
NVL (expr1, expr2)
In the
syntax:
• expr1 is the source value or expression
that may contain a null
• expr2 is the target value for converting
the null
You can use
the NVL function to convert any data type, but the return value is always the
same as the data type of expr1.
Using
the NVL Function
To
calculate the annual compensation of all employees, you need to multiply the
monthly salary by 12 and then add the commission percentage to the result:
SELECT last_name, salary,
commission_pct,
(salary*12) + (salary*12*commission_pct) AN_SAL
FROM employees;
Notice that
the annual compensation is calculated for only those employees who earn a
commission. If any column value in an expression is null, the result is null.
To calculate values for all employees, you must convert the null value to a
number before applying the arithmetic operator.
Using
the NVL2 Function
The NVL2
function examines the first expression. If the first expression is not null,
then the NVL2 function returns the second expression. If the first expression
is null, then the third expression is returned.
Syntax
NVL2(expr1, expr2,
expr3)
In the
syntax:
• expr1 is the source value or expression
that may contain a null
• expr2 is the value that is returned if expr1
is not null
• expr3 is the value that is returned if expr1
is null
SELECT last_name,
salary, commission_pct,
NVL2(commission_pct,
'SAL+COMM', 'SAL') income
FROM employees
WHERE department_id IN (50, 80);
In the
example shown above, the COMMISSION_PCT column is examined. If a value is
detected, the second expression of SAL+COMM is returned. If the COMMISSION_PCT
column holds a null value, the third expression of SAL is returned.
The
argument expr1 can have any data type. The arguments expr2 and expr3
can have any data types except LONG. If the data types of expr2 and expr3
are different, the Oracle server converts expr3 to the data type of expr2
before comparing them, unless expr3 is a null constant. In the latter
case, a data type conversion is not necessary. The data type of the return
value is always the same as the data type of expr2, unless expr2
is character data, in which case the return value’s data type is VARCHAR2.
Using
the NULLIF Function
The NULLIF
function compares two expressions. If they are equal, the function returns a
null. If they are not equal, the function returns the first expression.
However, you cannot specify the literal NULL for the first expression.
Syntax
NULLIF (expr1, expr2)
In the
syntax:
• NULLIF compares expr1 and expr2.
If they are equal, then the function returns null. If they are not, then the
function returns expr1. However, you cannot specify the literal NULL for
expr1.
SELECT first_name, LENGTH(first_name)
"expr1",
last_name, LENGTH(last_name) "expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM
employees;
In the
example shown above, the length of the first name in the EMPLOYEES table is
compared to the length of the last name in the EMPLOYEES table. When the
lengths of the names are equal, a null value is displayed. When the lengths of
the names are not equal, the length of the first name is displayed.
Note: The NULLIF function is logically
equivalent to the following CASE expression. The CASE expression is discussed
on a subsequent page:
CASE WHEN expr1 = expr 2
THEN NULL ELSE expr1 END
Using
the COALESCE Function
The COALESCE
function returns the first non-null expression in the list.
•
The
advantage of the COALESCE function over the NVL function is that the COALESCE
function can take multiple alternate values.
•
If
the first expression is not null, the COALESCE function returns that
expression; otherwise, it does a COALESCE of the remaining expressions.
Syntax
COALESCE (expr1, expr2,
... exprn)
In the
syntax:
• expr1 returns this expression if it is
not null
• expr2 returns this expression if the
first expression is null and this expression is not null
• exprn returns this expression if the preceding
expressions are null
Note that all expressions must be of the
same data type.
SELECT last_name,
employee_id,
COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_id),
'No commission and no manager')
FROM employees;
If the manager_id
value is not null, it is displayed. If the manager_id value is null, then the commission_pct
is displayed. If the manager_id and commission_pct values are null, then “No
commission and no manager” is displayed. Note, TO_CHAR function is applied so
that all expressions are of the same data type.
