SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to avoid nulls with greatest function?

Alessandro RossiSep 11 2009 — edited Jun 4 2013

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

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 2 2013

Added on Sep 11 2009

27 comments

49,993 views