Toad World® Forums

AW: Brain fry OT: NVL vs. Coalesce


#1

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? :wink:

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;


#2

Are you doing the PL/SQL challenge? http://www.plsqlchallenge.com

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;

#3

Morning Patrick,

  1. NVL is Oracle specific and not SQL Standard. Coalesce is standard.

  2. 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.

  3. 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


#4

Thank you Norm!

The link is also very useful.

Patrik

Von: toad@yahoogroups.com [mailto:toad@yahoogroups.com] Im Auftrag von Dunbar,
Norman (Capgemini)
Gesendet: Dienstag, 1. März 2011 10:52
An: toad@yahoogroups.com
Betreff: RE: [toad] Brain fry OT: NVL vs. Coalesce

Morning Patrick,

  1. NVL is Oracle specific and not SQL Standard. Coalesce is standard.

  2. 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.

  3. 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


#5

On 01/03/11 18:42, Böschenstein Patrik wrote:

Thank you Norm!
Welcome.


Cheers,
Norm. [TeamT]


#6

Try it again on a column though, I rather suspect

it will blow up in your face, exactly as NVL does

Heh, you mean like this:

Select coalesce(null,1/0) from dual;

ERROR at line 1:

ORA-01476: divisor is equal to zero

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.

Roger S.