This discussion is archived
1 2 Previous Next 27 Replies Latest reply: Jun 4, 2013 12:29 PM by xerces8 RSS

How to avoid nulls with greatest function?

Alessandro Rossi Journeyer
Currently Being Moderated
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
  • 1. Re: How to avoid nulls with greatest function?
    Twinkle Expert
    Currently Being Moderated
    try this,

    SQL> select greatest('hello','zzzzz','aaaaa',nvl(' ',0)) from dual;

    GREAT
    -----
    zzzzz
  • 2. Re: How to avoid nulls with greatest function?
    Sven W. Guru
    Currently Being Moderated
    Hm... is -999999 greater than nothing?
    Answer: It depends (on your specific busines requirements).

    Solution is simple: Add a nvl around each of your values and replace it with the value that suits your specific needs.

    Simple thing to remember: each function will return NULL when one of the parameters is NULL.

    There is only a handful of exceptions:
    NVL, NVL2, DECODE, LNNVL, NULLIF, COALESCE and CASE

    Edited by: Sven W. on Sep 11, 2009 11:01 AM
  • 3. Re: How to avoid nulls with greatest function?
    Karthick_Arp Guru
    Currently Being Moderated
    NULL means an Unknown value correct? So when we pass an Unknown value to a function that compares values it cant handle an Unknown value so it just says the result is als Unknows.

    Only option coming in my mind is to use NVL with all the passing values.
  • 4. Re: How to avoid nulls with greatest function?
    Alessandro Rossi Journeyer
    Currently Being Moderated
    That's not a solution!!

    What I posted is just a simple way to show how it works. When I call the greatest function I don't have idea about the value of the expressions I put in it, else I would also know the greatest too without using the greatest function.

    I hope this is won't offend you but this problem, in my opinion, looks really serious.
    Thanks anyway.

    Bye Alessandro
  • 5. Re: How to avoid nulls with greatest function?
    Twinkle Expert
    Currently Being Moderated
    I would also know the greatest too without using the greatest function.
    I know that, but the only way to avoid null values is use NVL.
    you can put those columns in NVL where there are chances of having null values.(columns which are not primary keys, or do not have not null constraint).

    Also check this
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:524526200346472289

    If this doesnot satisfy your question then give us similar scenerio which explains
    When I call the greatest function I don't have idea about the value of the expressions I put in it
    Edited by: Twinkle on Sep 11, 2009 3:36 PM

    Edited by: Twinkle on Sep 11, 2009 3:40 PM
  • 6. Re: How to avoid nulls with greatest function?
    Sven W. Guru
    Currently Being Moderated
    The MAX function will also give you the greatest value. It does however compare rows not columns. Good thing is it ignores NULL values. So you might think how to transpose/move your different values into a nice and database appropriate row structure.
  • 7. Re: How to avoid nulls with greatest function?
    Alessandro Rossi Journeyer
    Currently Being Moderated
    Sven W. wrote:
    Hm... is -999999 greater than nothing?
    Answer: It depends (on your specific busines requirements).
    Yes that's right! My business requirements says that the expressions I pass as null to greatest are values that not exist, so I need to not consider nulls.
    Solution is simple: Add a nvl around each of your values and replace it with the value that suits your specific needs.

    Simple thing to remember: each function will return NULL when one of the parameters is NULL.

    There is only a handful of exceptions:
    NVL, NVL2, DECODE, LNNVL, NULLIF, COALESCE and CASE
    I'm already using a substr(greatest(x||exp1, ... x||expn),2) workaround in my project but this is very ugly perhaps this workaround makes a simple task to become more complex and sensibly longer on heavy loads.


    Bye Alessandro
  • 8. Re: How to avoid nulls with greatest function?
    Sven W. Guru
    Currently Being Moderated
    I really think you should find a way to use MAX instead of GREATEST.
  • 9. Re: How to avoid nulls with greatest function?
    Alessandro Rossi Journeyer
    Currently Being Moderated
    Sven W. wrote:
    I really think you should find a way to use MAX instead of GREATEST.
    To use max i should pivot my table first and that would be more complex again.

    I'll wait for the next new oracle useful functions GREATESTNNV and LEASTNNV for the next few centuries.


    Thanks anyway.
    Bye Alessandro

    Edited by: Alessandro Rossi on 11-set-2009 12.27
  • 10. Re: How to avoid nulls with greatest function?
    BluShadow Guru Moderator
    Currently Being Moderated
    Alessandro Rossi wrote:
    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.
    "everybody"? you sure about that? ;)

    As Karthick correctly points out, NULL is the equivalent of an "unknown" value.

    If you have a bag of numbered balls with 3 numbers in it, you know two of the numbers but the 3rd one is unknown to you, which of those numbers is the greatest? Of course the answer is unknown. That sounds perfectly acceptable to me and is exactly what I expect.
  • 11. Re: How to avoid nulls with greatest function?
    Sven W. Guru
    Currently Being Moderated
    Alessandro Rossi wrote:
    Sven W. wrote:
    I really think you should find a way to use MAX instead of GREATEST.
    To use max i should pivot my table first and that would be more complex again.
    I think this might normalize your data. the little you have shown lets me think that it is pretty de-normalized.

    Greatest is rarely used. Max is used all the time.
    The simple reason is: Data that on wants to compare comes usually in rows, not in columns. Especially for an unknown number of data.

    Edited by: Sven W. on Sep 11, 2009 2:45 PM
  • 12. Re: How to avoid nulls with greatest function?
    Alessandro Rossi Journeyer
    Currently Being Moderated
    If you have a bag of numbered balls with 3 numbers in it, you know two of the numbers but the 3rd one is unknown to you, which of those numbers is the greatest? Of course the answer is unknown. That sounds perfectly acceptable to me and is exactly what I expect.
    Your example is not well proposed. I know you interpret null as unknown but in many cases null means missing. So ....

    You have a bag with some numbered balls and some unnumbered balls. What could be the greatest number you can spot on the balls?

    Can you spot the difference between unknown and nonexistent?

    I was just feeling the need of a greatest (and least function too) that ignores null values, instead of invalidating all my job.

    If you don't, just ignore this thread and keep working on your things and have a good life but don't leave such a stupid message! I have a problem and you can't come to me and say that is not a problem, because the problem is there.

    And why don't you say that it's strange that MAX() and MIN() ignore nulls then? If I would think with your head, I would also say that if I have an unknown value in a set I can't say witch one is the greatest or the least and then they should return null like GREATEST() and LEAST() do giving an "ACCEPTABLE" result . Isn't this right?


    Bye Alessandro

    Edited by: Alessandro Rossi on 11-set-2009 14.55
  • 13. Re: How to avoid nulls with greatest function?
    Alessandro Rossi Journeyer
    Currently Being Moderated
    It's not denormalized data it's just a table with some date fields that has to be joined with another one on the greatest criteria.

    I don't think this could be so unusual.


    Bye Alessandro
  • 14. Re: How to avoid nulls with greatest function?
    Karthick_Arp Guru
    Currently Being Moderated
    Can you spot the difference between unknown and nonexistent?
    In RDBMS

    UnKnown = The Data exist in the database but it value is Unknown = NULL
    NonExistant = The Data does not exist in the database

    I guess you are mixing it here.
    And why don't you say that it's strange that MAX() and MIN() ignore nulls then?
    ANSI has defined Aggrigate functions to skip NULL.

    A system has been created and the world has accepted the system. If you go against it then its your call. There is no point blaming the system.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points