ORA-24347: Warning of a NULL column in an aggregate function
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

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

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    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
    Sonali

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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 ;

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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]

  4. #4
    Join Date
    Jan 2001
    Posts
    318
    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]
    Sonali

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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]

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