| How to avoid nulls with greatest function? [message #421856] |
Fri, 11 September 2009 03:59  |
 |
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 #421859 is a reply to message #421856] |
Fri, 11 September 2009 04:07  |
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 |
|
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 #421964 is a reply to message #421856] |
Sat, 12 September 2009 09:59 |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
