1 2 Previous Next 27 Replies Latest reply: Jun 4, 2013 2:29 PM by xerces8 Go to original post RSS
      • 15. Re: How to avoid nulls with greatest function?
        Aketi Jyuuzou
        We have to use coalesce any times. :D
        http://www.geocities.jp/oraclesqlpuzzle/10-179.html


        There is a good news that greatest of postgreSQL igonre nulls. B-)
        http://www.postgresql.jp/document/current/html/functions-conditional.html

        Greatest of Oracle and db2 do not ignore nulls
        • 16. Re: How to avoid nulls with greatest function?
          Frank Kulash
          Hi,

          You can use MIN and MAX with odci types to get what you want.

          For example:
          SELECT     MAX (column_value)
          FROM     TABLE ( sys.odcinumberlist ( -999
                                              , 1
                                        , 999
                                        , NULL
                                        )
                     )
          ;
          If the arguments are coming from some table, you can do a scalar sub-query, like this:
          SELECT  ename
          ,     job
          ,     ( SELECT  MIN (column_value)
                 FROM       TABLE ( sys.odcivarchar2list ( ename
                                                , job
                                          , NULL
                                          , 'FOO'
                                          )
                         )
               ) AS least_string
          FROM     scott.emp
          ;
          Output from the last query:
          NAME      JOB       LEAST_STRING
          ---------- --------- ---------------
          SMITH      CLERK     CLERK
          ALLEN      SALESMAN  ALLEN
          WARD       SALESMAN  FOO
          JONES      MANAGER   FOO
          MARTIN     SALESMAN  FOO
          BLAKE      MANAGER   BLAKE
          CLARK      MANAGER   CLARK
          SCOTT      ANALYST   ANALYST
          KING       PRESIDENT FOO
          TURNER     SALESMAN  FOO
          ADAMS      CLERK     ADAMS
          JAMES      CLERK     CLERK
          FORD       ANALYST   ANALYST
          MILLER     CLERK     CLERK
          • 17. Re: How to avoid nulls with greatest function?
            BluShadow
            Alessandro Rossi wrote:
            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?
            Ah, but that's where you're going wrong.
            You are providing a NULL value as a parameter. Therefore it is not nonexistent. You are telling the function there is a value, but that the value is unknown. If it were nonexistent then you would have only provided 2 parameters instead of 3.

            If you're referring to NULL in programming languages (especially Oracle SQL and PL/SQL), by considering null to always mean "unknown" you will always be on the right track to understanding. Consider it nonexistent and you will confuse yourself. Oracle treats it as "unknown" and that is how I understand it and, in truth, you'd be better off understanding it that way too. ;)
            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.
            I'm sorry you have taken offence at it (your problem not mine). I was merely explaining a valid way to consider NULLs within Oracle and other programming languages. I have always considered NULL to mean Unknown and that has always worked. The only time I have ever encountered people experiencing problems understanding something because of NULL is when they do not consider it to mean "unknown".

            The analogy of the bag was intended to be helpful.
            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?
            Part of SQL throughout history has been that aggregate functions typically eliminate nulls from their calculations. It's an ongoing area of controversy and can lead to some unpredictable results... e.g.

            The average of 4 rows of values, one containing null...
            SQL> ed
            Wrote file afiedt.buf
            
              1  with t as (select 150 as x from dual union all
              2             select 200 from dual union all
              3             select 250 from dual union all
              4             select null from dual)
              5  --
              6  select avg(x)
              7* from t
            SQL> /
            
                AVG(X)
            ----------
                   200
            ... is calculated as the sum of the non-null values divided by the number of non-null rows.

            However you may typically expect the null row to be counted, such that the result is the sum of all the values divided by the total number of rows..., effectively treating the null as a zero...
            SQL> ed
            Wrote file afiedt.buf
            
              1  with t as (select 150 as x from dual union all
              2             select 200 from dual union all
              3             select 250 from dual union all
              4             select 0 from dual)
              5  --
              6  select avg(x)
              7* from t
            SQL> /
            
                AVG(X)
            ----------
                   150
            
            SQL>
            ... which gives a different result.

            That's just one of the things with aggregate functions. Always has been. To be consistent they too should return NULL, and then people would be forced to enter a value on their database to signify a 'default' of some sort i.e. 0 where the data is non-existent.

            So, yes, I agree with you that this isn't ideal. As such, there are just the two things to remember...

            1) Aggregate functions eliminate nulls
            2) Nulls everwhere else should be treated as "unknown".

            If you really wanted to get into the nitty gritty of it you could argue that both of these should return null too..
            SQL> select 1+null from dual;
            
                1+NULL
            ----------
            
            
            SQL> select 'fred'||null from dual;
            
            'FRE
            ----
            fred
            
            SQL>
            ;)
            • 18. Re: How to avoid nulls with greatest function?
              Sven W.
              Alessandro Rossi wrote:
              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
              You're right if you have just some date fields. That would be a quite typical case. In this case you always go with the NVL logic or with some special CASE handling. I had the impression that you have many more fields then just "some".

              probably that impression cam from this example of yours (which somehow doesn#t look like date values...)
              substr(greatest(x||exp1, ... x||expn),2)
              Edited by: Sven W. on Sep 11, 2009 3:38 PM
              • 19. Re: How to avoid nulls with greatest function?
                Hoek
                1) Aggregate functions eliminate nulls
                Hmz...Thanks! You've hereby inspired me to start writing a script for a Kill Null - movie ;)

                starts whistling
                • 20. Re: How to avoid nulls with greatest function?
                  Alessandro Rossi
                  So if you may see a valid point on missing a sort of GREATESTNNV and LEASTNNV functions there is nothing else to say. I agree with your point too, I know that null values should be avoided when possible, but it's not always possible to find perfectly designed environments to work on. I opened this thread to look for a solution of my problem and instead of finding it I had a discussion with you. I hope you don't feel offended about it, because it was not my intention.


                  Bye Alessandro
                  • 21. Re: How to avoid nulls with greatest function?
                    66470
                    I cannot see a need of GREATESTNNV or LEASTNNV as you can easily mask your parameters with the NVL function and choose an approriate value to replace the nulls...

                    And even if we agreed in the point that it would be nice to have those functions, it would not help you to solve your problem, as this functions are not around yet nor in 11g nor in release 2 and i doubt they will in oracle 12...
                    • 22. Re: How to avoid nulls with greatest function?
                      Alessandro Rossi
                      Oh you spot it!

                      I just wanted to show that I knew a workaround for it too, but it was not what I was looking for.

                      I didn't want to expose my implementation just because I didn't consider it good for the examples I proposed. But if NLS_DATE_FORMAT would be 'yyyy-mm-dd hh24:mi:ss' the subtr method would be fine.


                      This is code I really used
                      nullif(greatest(nvl(g_scade,date'0000-01-01'),nvl(g_doceme,date'0000-01-01'),nvl(g_inser,date'0000-01-01')),date'0000-01-01')
                      But using nullif(nvl) method in this case would fail without noticing any error while using the subtr one you're only limited by the fact that you can't use strings longer than 3999 bytes.
                      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> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
                        2  /
                      
                      Session altered.
                      
                      SQL>
                      SQL> select nullif(greatest(nvl(null,date'0001-01-01'),nvl(date'0001-01-01',date'0001-01-01')),date'0001-01-01')
                        2  from dual
                        3  /
                      
                      NULLIF(GREATEST(NVL
                      -------------------
                      
                      
                      SQL>
                      SQL> select substr(greatest('x'||null,'x'||date'0001-01-01'),2)
                        2  from dual
                        3  /
                      
                      SUBSTR(GREATEST('X'
                      -------------------
                      0001-01-01 00:00:00
                      
                      SQL>
                      Bye Alessandro

                      Edited by: Alessandro Rossi on 11-set-2009 16.49
                      • 23. Re: How to avoid nulls with greatest function?
                        Alessandro Rossi
                        It is a missing feature.
                        • 24. Re: How to avoid nulls with greatest function?
                          Alessandro Rossi
                          horten69 wrote:
                          I cannot see a need of GREATESTNNV or LEASTNNV as you can easily mask your parameters with the NVL function and choose an approriate value to replace the nulls...
                          Don't worry about it your needs are not like mine. We're all different from each other and it's not that bad. I wouldn't be that happy to meet just people like me I like comparing my opinions with others and of wouldn't be different from mine I wouldn't need it.
                          And even if we agreed in the point that it would be nice to have those functions, it would not help you to solve your problem, as this functions are not around yet nor in 11g nor in release 2 and i doubt they will in oracle 12...
                          Nothing more right than that but probably this thread could inspire someone there to introduce them. As someone said there is a little competitor who already did it, so there could be one time when Oracle would do it too.

                          For this moment I'll wait for it or for the time when Postgre SQL will lead the database market.


                          Bye Alessandro
                          • 25. Re: How to avoid nulls with greatest function?
                            user12019680
                            just my two cents

                            I had to do this to get the greatest of four dates and I used nvl and greatest

                            I didn't want to use some arbitrary date for the second argument of the nvl, I was not sure how far back the dates went

                            I was lucky in that one of my dates was a mandatory field so I just used that one for the second argument

                            this seems to work for me

                            select max(greatest(
                            date1, -- this is the mandatory one
                            nvl(date2,date1),
                            nvl(date3,date1),
                            nvl(date4,date1)))
                            from mytab
                            where key=...
                            • 26. Re: How to avoid nulls with greatest function?
                              Paul  Horth
                              Why are you responding to a 4 year old thread?
                              • 27. Re: How to avoid nulls with greatest function?
                                xerces8
                                Maybe because he has the best answer to the question?
                                Yep that's it. An excellent answer to a thread where half of replies are useless chit chat and also some proposals that might work in certain circumstances.
                                His solution also works only in certain circumstances, so at least it nicely complements the other answers.
                                10 points from me...
                                1 2 Previous Next