Error converting data type varchar to numeric in sql when value is compared in where clause

Question

I have a dataset of decimal values that are stored as varchar. I need to return all of the values that are less than 8.0. This is my query:

;WITH CTE
AS
(
SELECT PatientEncounterID, CAST(value as numeric(3,1)) As Value
from Observation 
where Term = 'HGBA1C' AND isnumeric(value) = 1
)
SELECT *
FROM CTE
WHERE Value < 8.0

When I run this query I get the error:

converting data type varchar to numeric

When I run the query without the where clause the values range from 5.0 to 16.9. The other column is just an identity field. The Value field is converted to a number in the CTE query.
Why can the where clause error?

UPDATE The total rows is 228. There should be 144 returned but only 39 are returned.

Be careful using ISNUMERIC to isolate values that are actually numbers. Things "1e8", '¢', '$.' will all evaluate to 1 with isnumeric but will throw an exception with your predicate. To me the biggest issue you have is storing what you determine as decimal in a varchar column. You obviously have some values that are not actually decimals. — Sean Lange
– Sean Lange, Commented Dec 14, 2016 at 22:04

Felix Pamittan · Accepted Answer · 2016-12-14 21:47:47Z

3

This is a result of the query optimizer deciding to perform the query as a single query. It combined all of your WHERE conditions and chooses to perform the Value < 8.0 check before the ISNUMERIC check. To get around this, you need to filter all numeric values first and put it in a temporary table:

INSERT INTO #TempTable
SELECT *
FROM Observation
WHERE
    Term = 'HBGAIC'
    AND ISNUMERIC(Value) = 1;

SELECT *
FROM  #TempTable
WHERE CAST(Value AS NUMERIC(3, 1)) < 8.0;

answered Dec 14, 2016 at 21:47

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gloria Santin Over a year ago

That worked! Wow...would have not thought of that. I thought that is the whole point of a CTE. Thanks!

Collectives™ on Stack Overflow

Error converting data type varchar to numeric in sql when value is compared in where clause

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related