simple NVL question

[Glenstr]

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?

[marist89]

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

[chrisrlong]

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

[jmodic]

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".