CMCITD
asked on
SQL CAST in a Where Statement--that deals with non-numeric entries
select AV.Name, AV.UniquePublicIdentifier, AP.PrimaryCareID AS PCP, AP.AttendID AS OrderingProvider, OMM.OrderDateTime, LisSpecimenTests, AV.ReasonForVisit, RD.DataItemID, RD.ResultInfo from OmOrd_LisSpecimens OMS
INNER JOIN OmOrd_Main OMM
ON OMM.OmOrdID = OMS.OmOrdID
INNER JOIN livendb.dbo.AdmVisits AV
ON OMM.VisitID = AV.VisitID
INNER JOIN livendb.dbo.AdmProviders AP
ON AP.VisitID = OMM.VisitID
INNER JOIN EmrAcctItem_ResultedData RD
ON RD.VisitID = AV.VisitID
where AV.LocationID = 'ER' and RD.DataItemID like 'Glucose%' and RD.ResultInfo <= '70'
Above is my original code. The issue is, I cannot do a true >= comparison on RD.ResultInfo because it isn't an integer--so I'm getting all kinds of non <= 70 results. If I change the last line to;
where AV.LocationID = 'ER' and RD.DataItemID like 'Glucose%' and CAST(RD.ResultInfo as Integer) <= '70'
..I get the dreaded "failed when converting varchar" error message as some of the entries are non-numeric. I understand how to do a CASE statement in other areas, but I'm struggling to figure out how to add a ISNUMERIC clause to that CAST statement--when it's in a WHERE statement.
I'm ok ignoring any non-numeric entries for this query.
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Terrific, the 2nd suggestion did the trick!
CASE WHEN ISNUMERIC(RD.ResultInfo) = 1 THEN CAST(RD.ResultInfo AS INT) ELSE 0 END