Problem with case statement in sql procedure.

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

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