Arithmetic overflow error converting varchar to data type numeric

1
votes

First, let me state I have read various similar posts and haven't been able to identify the similarities between the problem that other posters have had with this error message and the situation I've encountered. Perhaps I'm not searching correctly, but here's the scenario. I'm trying to search for values in a table that are less than 70 when converted to a numeric value. Sometimes the value can be stored with a comma (i.e. 3,080 etc.) so I have a replace statement to remove the comma. The obsValue column in the queries below is varchar(2000) and I'm guessing that may have something to do with it. My initial query worked:

Select name, obsValue
From database.dbo.table
Where name in ('LDL') 
and isnumeric(obsvalue) = 1 
and cast(replace(obsvalue,',','') as decimal(18)) < 70

This brings back expected values, but it's not the only name I'm trying to search for. Other examples include ('LDL(CALC)'). Using a UNION statement will allow me to union queries together but unfortunately I don't control the application code and this is not an option. The only option I have available is using an IN clause, so ultimately the query will look like this when I'm searching for a variety of name values:

Select name, obsValue
From database.dbo.table
Where name in ('LDL', 'LDL(CALC)') 
and isnumeric(obsvalue) = 1 
and cast(replace(obsvalue,',','') as decimal(18)) < 70

And unfortunately doing it this way is where I get the error message. I apologize if this has already been answered elsewhere. Please link and I will give credit where credit is due.

3
sqlsql-server-2005isnumeric
what error are you getting? Also, for debugging, try breaking it into pieces to see which function is causing the error. Test running only the in() function, isnumeric(), and cast(). Probably only one part of your query is generating the error. - Beth
Maybe the decimal point?, what happens if you do your cast as DECIMAL(36,6) or something like that? - Lamak
@beth It's the title of the question, Arithmetic overflow error converting varchar to data type numeric. Just saw your edit as well, the issue isn't in any one function, the issue is when I try to include more than one value in the IN clause. Ex. IN ('LDL') works, IN ('LDL(CALC)') works, IN ('LDL', 'LDL(CALC)') fails. @Lamak DECIMAL(36,6) causes the same error - akousmata
What if you try to cast it to a bigger datatype?, what happens if you cast it as MONEY? - Lamak
Then you have a value that is really too big to be casted as numeric, maybe you can find that value doing a SELECT on your table and ordering by LEN(obsValue) DESC - Lamak

3 Answers

1
votes

There may be values in obsvalue that are too big for your cast( as decimal) function but you don't care about anyways because they don't meet your in() criteria.

Try applying the cast() in a subquery, limiting your query to obsvalues you actually need to convert.

Also, since the commas only exist if the value is greater than 999, and you're testing for values less than 70, you don't need the replace. In fact, you could exclude any row containing a comma because you know it's too high.

1
votes

The problem is that SQL Server is optimizing the query differently when there are multiple items in the IN clause. You can use a CASE statement to prevent the optimization

SELECT name, obsvalue
FROM database.dbo.table 
WHERE (CASE WHEN ( isnumeric(obsvalue) = 1 
             AND name in ('LDL', 'LDL(CALC)')) 
       THEN cast(replace(obsvalue,',','') as decimal(18))
       ELSE null END) < 70
1
votes

You can replace the IsNumeric(obsvalue) with (select obsvalue where isnumeric(obsvalue) = 1).

Select name, obsValue
From database.dbo.table
Where name in ('LDL', 'LDL(CALC)') 
and isnumeric(obsvalue) = 1 
and cast(replace((select obsvalue where isnumeric(obsvalue) = 1),',','') as decimal(18)) < 70