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

We have a customer who is getting
ORA-24347: Warning of a NULL column in an aggregate function

Query they are running is:

SELECT (NVL(Sum(BudgetDet_Fixed_Amt),0)), (NVL(Sum(BudgetDet_Actual_Amt),0)),
(NVL(Sum(BudgetDet_EAC_Amt),0)), (NVL(Sum(BUDGETDET_LASTUPDATED_AMT),0))
FROM BudgetDetail
WHERE BudgetDet_BudgetRep_ID=666
and ((BudgetDet_Type=2 and BUDGETDET_MILESTONE in (0,10))or BudgetDet_Type=4)
and BudgetDet_Project_ID=3205 ;

They have Oracle 8.1.7.2. I looked in Metalink for this error message, they say that this bug was created in 8.1.6 and is fixed in 8.1.7. Does any one know why I am getting this error ? Did Oracle really fixed this ?

They asked me to change it to so by doing that they will not get this error
Select SUM(NVL(xx,0)), ....


Instead of
Select NVL(SUM(xx), 0), ....

(NVL(SUM(B (NVL(SUM(B
---------- ----------
0 0

But I cannot do it because if there is no row returned by this select SUM(NVL(.... returns NULL

SUM(NVL(BU
----------



Any help on this would be really appreciated.

Thanks
Sonali

Your select equal ABSLUTLY:

SELECT Sum(BudgetDet_Fixed_Amt),
Sum(BudgetDet_Actual_Amt),
Sum(BudgetDet_EAC_Amt),
Sum(BUDGETDET_LASTUPDATED_AMT)
FROM BudgetDetail
WHERE BudgetDet_BudgetRep_ID=666
and ((BudgetDet_Type=2 and BUDGETDET_MILESTONE in (0,10))or BudgetDet_Type=4)
and BudgetDet_Project_ID=3205 ;

If you want use NVL try:

SELECT
Sum(NVL(BudgetDet_Fixed_Amt,0)), Sum(NVL(BudgetDet_Actual_Amt,0)),
Sum(NVL(BudgetDet_EAC_Amt,0)), Sum(NVL(BUDGETDET_LASTUPDATED_AMT,0))
FROM BudgetDetail
WHERE BudgetDet_BudgetRep_ID=666
and ((BudgetDet_Type=2 and BUDGETDET_MILESTONE in (0,10))or BudgetDet_Type=4)
and BudgetDet_Project_ID=3205 ;

Try this:

select decode(sum(sal),NULL,0, sum(sal)) from emp;

Partially I would blame Dr.E.F.Codd who defined 12 rules of RDBMS.

Each RDBMS vendor defines NULL differently. That is why you get the error.


[Edited by tamilselvan on 10-24-2001 at 04:35 PM]

Thanks tamilselvan, I will try if I can use DECODE in there.
The problem is this is all over application and it will not be easy to change it. Also I had asked similar question long back relating to performance.. here is the link to it.

http://www.dbasupport.com/forums/sho...?threadid=8934

So as Jmodic has said..
"SUM ignores the NULL values and only sums the non-null values"

I was expecting NVL(Sum(col1),0) to work but it is giving error message at the customer site not here. They have Oracle 8.1.7.2.1 and we have Oracle 8.1.7 here.

ORA-24347: Warning of a NULL column in an aggregate function because it cannot handle NULLvalues in the column while summing up.

And then there is other issue when the select does not return any row.

Thanks
Sonali

[Edited by sonaliak on 10-29-2001 at 03:46 PM]

I saw your earlier thread.
When you add two sums, then add NVL functions after the sum is done. Otherwise you will not see correct result, because if one of the sums is NULL (empty string), then it can't be added to a numeric value. So the result will become NULL (empty string).

Also remember that prior to version 7.3, NULL was undefined in Oracle. Where as now it is empty string.

Following example may help you:
SELECT SUM(SAL)+SUM(COMMISSION) FROM EMP;
If SUM(SAL) is 9000 and SUM(COMMISSION) is NULL, the result will be NULL.
To get correct result, add NVL before SUM.
SELECT NVL(SUM(SAL),0)+NVL(SUM(COMMISSION),0)
FROM EMP;

I hope you can solve your problem.

Another way of solving this problem is declare a default value as 0 (ZERO) to the number columns.


[Edited by tamilselvan on 10-25-2001 at 12:10 PM]