SQL Arithmetic Overflow error converting varchar to decimal when limiting values in where clause

Question

I have a CTE query that converts a varchar field into a decimal. However, in the SELECT after the CTE I want to limit the values returned. This is when I get the

arthimetic overflow error

This is the query:

;WITH CTE
AS
(
SELECT PP.PatientProfileID, 
        ROUND(CAST( CASE WHEN ISNUMERIC(O.Value) = 1 THEN O.Value END AS DECIMAL(4, 1)), 1) AS BmiValue
FROM PatientProfile PP RIGHT OUTER JOIN Encounter E1 ON PP.PatientProfileID = E1.PatientProfileID
        JOIN Procedures P on E1.PatientEncounterID = P.PatientEncounterID
        JOIN Observation O ON O.PatientProfileID = E1.PatientProfileID
WHERE P.CPTCode = '3008F' AND
    O.Term = 'BMI' AND O.Value IS NOT NULL
)
SELECT * 
FROM CTE
WHERE BmiValue >= 18.5 AND BmiValue < 25.0

When I limit the where clause to just: WHERE BmiValue >= 18.5 there is no error. When I only use WHERE BmiValue < 25.0 there is no error. Only when I use both expressions is there an arithmetic overflow error.

What is wrong with the query?

UPDATE As I stated in my comments, I am using SQL SERVER 2008 R2. I eliminate nulls in the CTE for the decimal values. When I remove the WHERE clause, all of the values for BmiValue appear to be decimal values.

If I narrow the selection of BmiValues to this WHERE clause: WHERE (BmiValue >= 18.5) AND (BmiValue < 30.0) There is no arithmetic overflow error. When I change the max value to this: WHERE (BmiValue >= 18.5) AND (BmiValue < 29.0) I get the arithmetic overflow error...weird.

@JacobH if the database is using a case sensitive collation those are in fact different aliases. — Sean Lange

SqlZim SqlZim · Accepted Answer · 2017-05-24T18:26:55

Since this is an overflow error, you can try increasing the size of the decimal(). No guarantees there is not another issue with the cast() though.

;with CTE as (
  select PP.PatientProfileID
    , ROUND(CAST(case when ISNUMERIC(O.Value) = 1 then O.Value end as decimal(19, 6)), 1) as BmiValue
  from PatientProfile PP
  right join Encounter E1 on PP.PatientProfileID = E1.PatientProfileID
  inner join Procedures p on E1.PatientEncounterID = p.PatientEncounterID
  inner join Observation O on O.PatientProfileID = E1.PatientProfileID
  where p.CPTCode = '3008F' and O.Term = 'BMI' and O.Value is not null
 )
select *
from CTE
where BmiValue >= 18.5 and BmiValue < 25.0

In Sql Server 2012 and up: each of these will return null when the conversion fails instead of an error.

SQL Arithmetic Overflow error converting varchar to decimal when limiting values in where clause

2 Answers