null value calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

null value calculation

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-25 : 08:41:24

rashid writes "hi,
i ran the follow command:
select #tempra.ranumber,#tempra.amountdue,#tempquickrec.totalrec,
#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   bal
1222      $1200    $1000     Null         null
2222      $3000    $3000     Null         null
3333      $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   bal
1222      $1200    $1000     Null         200
2222      $3000    $3000     Null         0
3333      $3000    $5000     $2000        0

i am using ms sql2000
thanks in advance
rashid"

Pethron
Starting Member

10 Posts

Posted - 2004-05-25 : 08:52:43

Try:
select #tempra.ranumber,#tempra.amountdue,#tempquickrec.totalrec,
#temparap.amountpaid,bal= ISNULL(amountdue,0) - ISNULL(totalrec,0)+ ISNULL(amountpaid,0) from #tempra
left join #tempquickrec on #tempquickrec.hrr = #tempra.ranumber
left join #temparap on #temparap.ranumber = #tempra.ranumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources