Problem with case statement in sql procedure.

I have the following sql procedure with a case statement on the b.originalamt column.  Without the case the answer is null.  But with the statement it acts like it isn't null.  

select OccuSeqNo,RTRIM(a.propertyid)+'-'+RTRIM(a.BldgId)+'-'+RTRIM(a.unitid)+'-'+rtrim(a.resiid) as fullid, case b.OriginalAmt when null then 0 else b.OriginalAmt end as  originalamt,c.resistatus,
cast(c.LeaseBegDate as DATE) as leaseBegin,cast(c.LeaseEndDate as DATE) as leaseEnd
from TAG_eSite.dbo.occupantheader a
left  outer join CollectionSystem.dbo.Collection_Master as b on RTRIM(a.propertyid)+'-'+RTRIM(a.BldgId)+'-'+RTRIM(a.unitid)+'-'+rtrim(a.resiid) = b.fullid
right outer join TAG_esite.dbo.lease c on a.PropertyId=c.PropertyId and a.BldgId=c.BldgId and a.UnitId=c.UnitId and a.ResiId = c.ResiId

where OccuLastName = @LName and OccuFirstName = @FName and Birthdate=@Bdate and RIGHT(OccuSSN,4)=@SSNum and c.ResiStatus<>'X'
END

How can I fix this.

thank you
mgmhicksAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
you need to use something like
     case when b.OriginalAmt is null
because you cannot compare with null directly
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Better in this case here: replace the case ... end with isnull(b.OriginalAmt).
PortletPaulEE Topic AdvisorCommented:
case [field] when (equal to) <<something>> then <<some outcome>>

in the format of case expression you have used there is an implied "equal to" after the word "when"

It is a rule that you cannot equal NULL because NULL's value is "indeterminate", i.e. it cannot be found & if you cannot find that value then you can also never find if something is equal to it.

So, there is a special set of features for handling NULLs

IS NULL establishes in a true/false sense if a value is in fact NULL

& as Qlemo has suggested there are also functions to help

in T-SQL you can use ISNULL()
or you can use the more generic COALESCE()

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.