simple NVL question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: simple NVL question

  1. #1
    Join Date
    Aug 2000
    Location
    Alberta
    Posts
    82
    It must be friday, my brain has locked up already...

    I want to do a query on a table that sums a column that may have null values. So I type in:

    select field1, nvl(sum(field2),0)
    from table
    group by field1

    ERROR:
    ORA-24347: Warning of a NULL column in an aggregate function

    Interestingly enough, the same query without the NVL function returns results when I run it in a TOAD window.

    I'm missing something obvious here... what is it?



  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    When you use:
    select field1, nvl(sum(field2),0)
    from table
    group by field1

    the nulls are in field2 and you are trying to sum them. If you want to sum the nulls as 0:
    select field1, sum(nvl(field2,0))
    from table
    group by field1

    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Very odd.

    The Sum() function ignores NULLs, so there is no ned for an interior nvl(). I have no idea what the error could possibly be for.

    The only need for an NVL() around the SUM() is if *all* the summed records are NULL (or no records match the criteria, which is not possible with a single-table GROUP BY.

    I'm able to do the same statement on a table I made with no error.

    So I guess I'm not being much help other than to say it *should* work

    Sorry,

    - Chris

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Interestly enough what docs says about this error.

    ORA-24347 Warning of a NULL column in an aggregate function
    Cause: A null column was processed by an aggregate function.
    Action: An OCI_SUCCESS_WITH_INFO is returned.

    I thing it was designed only as information message (particullarly the "Action:" line leeds me to this conclusion, although I'm not sure why this ORA message at all, as aggregate functions should not be bothered with nulls) and it was ment that you should still get the proper result, something like when you ocasionlly get "ORA-00000 normal, successful completion".
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width