Problem with case statement in sql procedure.

mgmhicks used Ask the Experts™
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 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'

How can I fix this.

thank you
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

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 Advisor
Top Expert 2015
Better in this case here: replace the case ... end with isnull(b.OriginalAmt).
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
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()

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial