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
case when b.OriginalAmt is null
because you cannot compare with null directly