Nulls in calculations

[Mohd Rashid Atan]

hi,
i ran the follow command:
select #tempra.ranumber,#tempra.amountdue,#tempquickrec.t otalrec,
#temparap.amountpaid,bal=amountdue - totalrec+amountpaid
from #tempra
left join #tempquickrec on #tempquickrec.hrr = #tempra.ranumber
left join #temparap on #temparap.ranumber = #tempra.ranumber
result:

ranumber amountdue totalrec amountpaid bal1222 $1200 $1000
Null null2222 $3000 $3000 Null null3333
$3000 $5000 $2000 0
i know null is not zero thus bal column is null.
this is because there is no corresponding record in the #temparap table
so how do i obtain the following result:
(ie Ranumber bal is not 0)

ranumber amountdue totalrec amountpaid bal1222 $1200 $1000
Null 2002222 $3000 $3000 Null 03333 $3000
$5000 $2000 0
i am using ms sql2000
thanks in advance
rashid

[Nigel Rivett]

bal= coalesce(amountdue.0) - coalesce(totalrec,0) +
coalesce(amountpaid,0)

Difficult to see what you are talking about as it's lost it's formatting
but that's a guess.

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Viktor Pryganov]

Hui!

select #tempra.ranumber,#tempra.amountdue,#tempquickrec.t otalrec,
#temparap.amountpaid,
bal=amountdue-isnull(totalrec,0)+isnull(amountpaid,0),
from #tempra
left join #tempquickrec on #tempquickrec.hrr = #tempra.ranumber
left join #temparap on #temparap.ranumber = #tempra.ranumber
-
exexe