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
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
you need to use something like
case when b.OriginalAmt is null
because you cannot compare with null directly
case when b.OriginalAmt is null
because you cannot compare with null directly
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()
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()
Premium Content
You need an Expert Office subscription to comment.Start Free Trial