Question: When should I use the nvl
function as opposed to the nvl2 function. What is the
difference between nvl and nvl2?
Answer: The nvl
function only has two parameters while the nvl
parameter has three arguments. The nvl2 like like
combining an nvl with a decode because you can transform a value:
-
NVL ( expr1 , expr2 ):
If expr1 is null, then NVL returns expr2. If expr1 is not null,
then NVL returns expr1.
-
NVL2 ( expr1 , expr2 , expr3 ):
If expr1 is null, then NVL2 returns expr3. If expr1 is
not null, then NVL2 returns expr2
As we see, the vanilla
nvl transformation
takes a NULL value and replaces it with a printable, useable value,
such as a zero or spaces:
select nvl(b.buffer_gets,0) – NVL replaces a NULL value with a
zero
select nvl(current_status, “Not disclosed”) – NVL replaces a
NULL value with a string
Conversely, the NVL2 clause accepts three
arguments, but ALWAYS transforms the input argument.
select NVL2(supplier_city, 'Completed', 'n/a')
from suppliers;
In this example, these statements are equivalent
because the nvl2 re-sets the input argument back to the original
value:
select nvl(commission_pct,0) 2 from employees;
select nvl2(commission_pct,commission_pct,0) 2
from employees;
Also see
these notes on advanced NULL operators in Oracle SQL.
Remember, that you can create an
index
on NULL values, using the nvl operator
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
