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
– Sean Lange, Commented May 24, 2017 at 18:34

SqlZim · Accepted Answer · 2017-05-24 18:26:55Z

4

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.

answered May 24, 2017 at 18:26

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

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

2 Comments

Gloria Santin Over a year ago

Can't use this answer. I am on SQL SERVER 2008 R2

Gloria Santin Over a year ago

Increasing the decimal cast fixed the problem! Thanks so much.

S3S · Accepted Answer · 2017-05-24 18:34:22Z

3

You're probably trying to run math on a null value.

1) ISNUMERIC() returns true on more than just numbers (including '-', '+', '$', etc.). Look into using TRY_CAST() instead.

2) Your CASE statement needs an ELSE clause. Otherwise, you're attempting to CAST any ISNUMERIC() = false (i.e., null values) to a DECIMAL.

Beyond that, use BETWEEN in your where clause -- just cleaner.

edited May 24, 2017 at 18:34

S3S

25.2k5 gold badges29 silver badges48 bronze badges

answered May 24, 2017 at 18:31

Chains

13.2k8 gold badges49 silver badges62 bronze badges

2 Comments

SqlZim Over a year ago

null would not cause the overflow error, and the between advice seems like a bad idea to me. What do between and the devil have in common? - Aaron Bertrand

Gloria Santin Over a year ago

I eliminate null values in the CTE...O.Value IS NOT NULL

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related