Avatar of mgmhicks
mgmhicks
 asked on

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
Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
Qlemo

you need to use something like
     case when b.OriginalAmt is null
because you cannot compare with null directly
SOLUTION
Qlemo

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
PortletPaul

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy