The question from yesterday was also bout NVL() that evaluate both parameters
and coalesce not. Among other executions like f1() AND f2() and f1() OR f2().
And I did not know about NVL()...... Some coincidence here.....
Groetjes,
Wim
On Tue, Mar 1, 2011 at 08:25, Böschenstein Patrik
wrote:
I began to hate NVL() since I had this terrible situation:
SELECT NVL( 1 , 1 / 0 ) FROM dual -- ORA-01476: Divisor is Null
SELECT COALESCE( 1 , 1 / 0 ) FROM dual -- ok
So NVL() seems to evaluate both sides. And I assume that NVL could be much
slower than Coalesce, particularly when “1/0” is a large function.
Does anyone know about that? Should we let NVL() rest in peace and go with
Coalesce() from now on? ;)
Thanks
Patrik
Von: toad@yahoogroups.com [mailto: toad@yahoogroups.com ] Im Auftrag von
Jeff Smith
Gesendet: Montag, 28. Februar 2011 20:11
An: toad@yahoogroups.com
Betreff: RE: [toad] Brain fry
If you want to know if it’s null, you can do a couple of things
1)Set the default value to show in grids for NULLs to something like
2)Use the nvl() function
For your date value, you’d need to convert it to text first…
select nvl(to_char( manager_id ), 'BOSS - he works for no one!' )
from HR . EMPLOYEES
where manager_id is null;
NVL is Oracle specific and not SQL Standard. Coalesce is standard.
NVL takes only two arguments, the first to be tested for NULL and the
next to be returned if the first is NULL. Both are evaluated, as you
note.
COALESCE takes multiple arguments, and will only evaluate up to the
first non-null argument.
So, while you get a barf on this:
SELECT NVL(1,1/0) FROM dual; -- ORA-01476: Divisor is Null
You don't on this because 1 is NOT NULL, so the second arg need not be
evaluated:
SELECT COALESCE(1,1/0) FROM dual;
Try it again on a column though, I rather suspect it will blow up in
your face, exactly as NVL does, when you hit the first NULL.
As to performance, I haven't done any comparisons yet. One day, I might.
However, there is a note here about performance of NVL when it evaluates
the second argument:
-and-coalesce
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
NVL is Oracle specific and not SQL Standard. Coalesce is standard.
NVL takes only two arguments, the first to be tested for NULL and the
next to be returned if the first is NULL. Both are evaluated, as you
note.
COALESCE takes multiple arguments, and will only evaluate up to the
first non-null argument.
So, while you get a barf on this:
SELECT NVL(1,1/0) FROM dual; -- ORA-01476: Divisor is Null
You don't on this because 1 is NOT NULL, so the second arg need not be
evaluated:
SELECT COALESCE(1,1/0) FROM dual;
Try it again on a column though, I rather suspect it will blow up in
your face, exactly as NVL does, when you hit the first NULL.
As to performance, I haven't done any comparisons yet. One day, I might.
However, there is a note here about performance of NVL when it evaluates
the second argument:
-and-coalesce
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged.
If you have received this message by mistake, please notify the sender
immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still
check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under
the Freedom of Information Act, Data Protection Act or for litigation. Email
messages and attachments sent to or from any Environment Agency address may also
be accessed by someone other than the sender or recipient, for business
purposes.
If we have sent you information and you wish to use it please read our terms and
conditions which you can get by calling us on 08708 506 506. Find out more about
the Environment Agency at www.environment-agency.gov.uk
Yea… no matter what function you use, so long as there’s a
possibility of having something divided by zero and a path that reaches that
evaluation, an appropriate error will be produced.
The developer should understand the data sufficiently that if dividing by zero
is possible, the code handles it gracefully.