Home » SQL & PL/SQL » SQL & PL/SQL » How to avoid nulls with greatest function? (10.2.0.4 on hpux 11v23)
How to avoid nulls with greatest function? [message #421856] Fri, 11 September 2009 03:59 Go to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Hi everybody

I just spot a quite strange behavior on the greatest function: when one of its parameters is null, it returns null.
I also don't think this is what everybody expects from it because there are several cases where this is unacceptable.

This is what happens on a 10.2.0.4 on hpux 11v23
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select greatest(-9999999999,1,99999999999,null)
  2  from dual
  3  / 
 
GREATEST(-9999999999,1,99999999999,NULL)
----------------------------------------
 
 
SQL> select greatest('hello','zzzzz','aaaaa','')
  2  from dual
  3  / 
 
G
-
 
 
SQL>


Does anybody know to ignore nulls in this cases?

Thanks
Bye Alessandro
Re: How to avoid nulls with greatest function? [message #421858 is a reply to message #421856] Fri, 11 September 2009 04:04 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since the documentation for the function says nothing about handling nulls I suspect you're stuck with using nvl.
Re: How to avoid nulls with greatest function? [message #421859 is a reply to message #421856] Fri, 11 September 2009 04:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The best way to think about NULL is that it means 'UNKNOWN'

If I ask you for the largest value out of -999, 1, 999 and an Unknown value, what is the answer?

The answer is that it is unknown what the largest value is.

The way to handle this is to use NVL on the values.
Re: How to avoid nulls with greatest function? [message #421906 is a reply to message #421858] Fri, 11 September 2009 10:33 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
cookiemonster wrote on Fri, 11 September 2009 11:04
Since the documentation for the function says nothing about handling nulls I suspect you're stuck with using nvl.


You got the point.

Probably there's nothing else to do than using workarounds. I just need to wait and wish for a new feature to do that.


Thanks anyway
Bye Alessandro

[Updated on: Fri, 11 September 2009 10:34]

Report message to a moderator

Re: How to avoid nulls with greatest function? [message #421910 is a reply to message #421856] Fri, 11 September 2009 10:45 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
As JRowbottom pointed out what you're seeing is the expected behaviour according to Oracle's definition of null. So I wouldn't bother waiting for them to do anything about it.
Re: How to avoid nulls with greatest function? [message #421964 is a reply to message #421856] Sat, 12 September 2009 09:59 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
null values are ignored in aggregate functions

Thus:

select max(a) from (
                     select 1 a from dual union all
                     select 2 from dual union all
                     select to_number(null) from dual
                   )
/

will return 2

null value is not ignored in single row functions

Thus

select greatest(1,2,to_number(null)) from dual
/

will return null

People often confuse greatest with max because these functions provide simliar information in seemingly similar situations. But the fact remains they are not similar at all and each follows the well prescribed rules for null handling.

Kevin
Previous Topic: whats wrong with this associative array
Next Topic: sql equivalent of dbms_utility.comma_to_table ?
Goto Forum:
  


Current Time: Mon Dec 05 08:46:35 CST 2016

Total time taken to generate the page: 0.08186 seconds