This is part from a stored procedure..
Set Temp_amount2= (SELECT nvl(SUM((NVL(Sum(Sched_Amount),0.0))+ (NVL(Temp_Amount1,0.0))),0)
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
Set Temp_amount2= nVL(Temp_Amount1,0)
WHERE Temp_Trancode = inTrancode
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.