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
The two
methods that are used to implement conditional processing (IF-THEN-ELSE logic)
in a SQL statement are the CASE expression and the DECODE function.
Note: The CASE expression complies with
the ANSI SQL. The DECODE function is specific to Oracle syntax.
CASE
Expression
CASE
expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2
THEN return_expr2
WHEN comparison_exprn THEN
return_exprn
ELSE else_expr]
END
CASE
expressions allow you to use the IF-THEN-ELSE logic in SQL statements without
having to invoke procedures.
In a simple
CASE expression, the Oracle server searches for the first WHEN ... THEN pair
for which expr is equal to comparison_expr and returns return_expr. If none of
the WHEN ... THEN pairs meet this condition, and if an ELSE clause exists, then
the Oracle server returns else_expr. Otherwise, the Oracle server returns a
null. You cannot specify the literal NULL for all the return_exprs and the else_expr.
All of the
expressions ( expr, comparison_expr, and return_expr) must be of the same data
type, which can be CHAR, VARCHAR2, NCHAR, or NVARCHAR2.
Using
the CASE Expression
SELECT
last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN
1.20*salary
ELSE
salary END
"REVISED_SALARY"
FROM employees;
In the SQL
statement above, the value of JOB_ID is decoded. If JOB_ID is IT_PROG, the
salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID
is SA_REP, the salary increase is 20%. For all other job roles, there is no
increase in salary.
The same
statement can be written with the DECODE function.
This is an
example of a searched CASE expression. In a searched CASE expression, the
search occurs from left to right until an occurrence of the listed condition is
found, and then it returns the return expression. If no condition is found to
be true, and if an ELSE clause exists, the return expression in the ELSE clause
is returned; otherwise, a NULL is returned.
SELECT last_name,salary,
(CASE WHEN salary<5000 o:p="o:p" ow="ow" then="then">
WHEN salary<10000 edium="edium" o:p="o:p" then="then">
WHEN salary<20000 o:p="o:p" ood="ood" then="then">
ELSE 'Excellent'
END) qualified_salary
FROM employees;
DECODE Function
DECODE(col|expression,
search1, result1
[, search2,
result2,...,]
[, default])
The DECODE
function decodes an expression in a way similar to the IF-THEN-ELSE logic that
is used in various languages. The DECODE function decodes expression
after comparing it to each search value. If the expression is the same
as search, result is returned.
If the
default value is omitted, a null value is returned where a search value does
not match any of the result values.
Using
the DECODE Function
SELECT
last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary)
REVISED_SALARY
FROM employees;
In the SQL
statement above, the value of JOB_ID is tested. If JOB_ID is IT_PROG, the
salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID
is SA_REP, the salary increase is 20%. For all other job roles, there is no
increase in salary.
The same
statement can be expressed in pseudocode as an IF-THEN-ELSE statement:
IF job_id = 'IT_PROG' THEN
salary = salary*1.10
IF job_id =
'ST_CLERK' THEN salary = salary*1.15
IF job_id = 'SA_REP' THEN
salary = salary*1.20
ELSE salary = salary
