DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Sum of 2 columns with values returns zero

  1. #1
    Join Date
    Jan 2001
    Posts
    318
    Hi all,
    This is part from a stored procedure..
    Update mwebtemp
    Set Temp_amount2= (SELECT nvl(SUM((NVL(Sum(Sched_Amount),0.0))+ (NVL(Temp_Amount1,0.0))),0)
    FROM mwebSchedule
    WHERE Sched_Work_ID in (select Work_ID from mwebWork where Work_Entity_Type in(4, 30)
    and Work_Int_Ext7 = 10 and Work_Int_Ext8 > 0)
    and Sched_Res_ID=Temp_Ent_ID and Sched_Category = 4500
    AND Sched_Date BETWEEN dtstartdate AND dtenddate
    and to_char(Sched_Date, 'D') between 2 and 6 GROUP BY Temp_Amount1 )
    Where Temp_Trancode = inTrancode;

    In this statement(above) if the sum(sched_amount) is null or 0 the whole select statement returns 0, even though temp_amount1 has value. In other words, if sum(sched_amount) = 0 or null and temp_amount1<>0 ( has some value say 15) then temp_amount2 gets value 0 and not 15. Why ?
    I just can't figure out why this is happening

    Update mwebtemp
    Set Temp_amount2= nVL(Temp_Amount1,0)
    WHERE Temp_Trancode = inTrancode
    and temp_amount2=0;

    Then I wrote this statement followed by 1st statement and now it gets the value when sum(sched_amount)= 0.

    I tried placing SUM and NVL in different places.. some crazy tries but nothing seems to work.


    thanks a lot

    Sonali





    Sonali

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    You don't need to use NVL with SUM fumction. Just say SUM. The query will return correct value.

  3. #3
    Join Date
    Jan 2001
    Posts
    318
    I tried this it still doesn't work..

    Update mwebtemp
    Set Temp_amount2= (SELECT Sum(Sched_Amount)+ (NVL(Temp_Amount1,0.0))
    FROM mwebSchedule
    WHERE Sched_Work_ID in (select Work_ID from mwebWork where Work_Entity_Type in(4, 30)
    )
    and Sched_Res_ID=Temp_Ent_ID and Sched_Category = 4500
    GROUP BY Temp_Amount1 )
    Where Temp_Trancode = inTrancode;

    I took out some portion of where clause to make it simpler..

    I also tried...this one doesn't work either.. What is wrong in my query ?


    Update mwebtemp
    Set Temp_amount2= (SELECT SUM(Sum(Sched_Amount)+ (NVL(Temp_Amount1,0.0)))
    FROM mwebSchedule
    WHERE Sched_Work_ID in (select Work_ID from mwebWork where Work_Entity_Type in(4, 30)
    )
    and Sched_Res_ID=Temp_Ent_ID and Sched_Category = 4500
    GROUP BY Temp_Amount1 )
    Where Temp_Trancode = inTrancode;
    Sonali

  4. #4
    Join Date
    Jan 2001
    Posts
    318
    I have one more question
    How do you Sum and NVL in select statement ?
    Do you do
    NVL(Sum(col1),0)

    OR do you do
    sum(nvl(col1,0))

    Which one of above is correct in the earlier query I have mentioned ?

    thanks
    Sonali

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Both are correct in a sence that they will both return you the same result. But NVL(Sum(col1),0) will require less CPU than SUM(NVL(col1,0)), so it will be quicker.

    SUM ignores the NULL values and only sums the non-null values. But if all the values to be summed are NULL then it will return NULL. So only in this case will the NVL function be executed if you use NVL(Sum(col1),0) to return you a zero, and it will be executed only once. But if you use SUM(NVL(col1,0)) then NVL will be applied to all NULL values before they will be added to the sum, but this is useless as the sum will not change if you add 0.
    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