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.property id)+'-'+RT RIM(a.Bldg Id)+'-'+RT RIM(a.unit id)+'-'+rt rim(a.resi id) 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.LeaseEnd Date as DATE) as leaseEnd
from TAG_eSite.dbo.occupanthead er a
left outer join CollectionSystem.dbo.Colle ction_Mast er as b on RTRIM(a.propertyid)+'-'+RT RIM(a.Bldg Id)+'-'+RT RIM(a.unit id)+'-'+rt rim(a.resi id) = 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
select OccuSeqNo,RTRIM(a.property
cast(c.LeaseBegDate as DATE) as leaseBegin,cast(c.LeaseEnd
from TAG_eSite.dbo.occupanthead
left outer join CollectionSystem.dbo.Colle
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
case when b.OriginalAmt is null
because you cannot compare with null directly