Arithmetic overflow error converting varchar to data type numeric.

Question

Arithmetic overflow error converting varchar to data type numeric.

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Amedeo Feroce SSChasing Mays Points: 607 More actions · Answer 1

I haven't created a derived table. I'm not a pro at this so help me understand what A derived table would accomplish.

Are you saying I would insert all of the records from existing tables into a derived table and then select only the records I want from a single derived table?

Or should I create several derived tables?

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 2

Actually, I was just looking at some old code and apparently SQL is "smart" enough to figure out what is going on so it will, in essence, collapse the query down so even a derived table won't work (or is not guaranteed to work). Unless someone knows of a way to force SQL to process a derived table first, then I think they only way you can get accurate results would be to use a Temp table or table variable.

Here is a quick sample of what I was talking about by using a derived table, for what it is worth:DECLARE @Foo TABLE (Val VARCHAR(10))

INSERT @Foo

SELECT '1'

UNION ALL SELECT '1.1'

UNION ALL SELECT 'and'

UNION ALL SELECT '54'

UNION ALL SELECT '0.9'

-- Fails

SELECT *

FROM @Foo

WHERE ISNUMERIC(Val) = 1

AND CAST(Val AS DECIMAL(18, 5)) > 0 -- Conversion Failure

-- Also fails

SELECT *

FROM

(

SELECT Val

FROM @Foo

WHERE ISNUMERIC(Val) = 1

) AS T

WHERE

CAST(Val AS DECIMAL(18, 5)) > 0

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 3

Just as an FYI, I did get the following query to work. However, I do not think I'd trust it to ALWAYS work in production:SELECT *

FROM

(

SELECT Val

FROM @Foo

WHERE ISNUMERIC(Val) = 1

) AS T

WHERE

ISNUMERIC(Val) = 1

AND CAST(Val AS DECIMAL(18, 5)) > 0

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

Have you tried your check on the result_value column with a CASE statement?

where

id_type = 'MR' and

resulted_test_desc = 'Hemoglobin' and

case

when isnumeric(result_value) = 0 or result_value is null

then 0

when convert(numeric(18,2),result_value) > 10.0

then 0

else 1 end = 1

Amedeo Feroce SSChasing Mays Points: 607 More actions · Answer 5

what drives me crazy about this error is that I don't think it's related to converting varchar to numeric as the error suggests.

I say this because I've been able to make the query work based on where I put specific WHERE statements. I've moved this line of code

hcl = (@Office)

from the bottom of the query to within the WHERE statement of each resultset and now it works.

I'm guessing it was some order of operations issue within the query analyzer but the error returned made me focus on code that had nothing to do with it.

Thanks to all that shared feedback.

sgmunson SSC Guru Points: 110640 More actions · Answer 6

One way you can ferret out a potential conversion error is to use a derived table where you eliminate any non-numeric values for your result_value field.

DECLARE @test-2 TABLE(

result_value varchar(20)

)

INSERT INTO @test-2(result_value)

SELECT '12324' UNION ALL

SELECT 'ABDCE' UNION ALL

SELECT '456.6235' UNION ALL

SELECT 'A3qetr3415' UNION ALL

SELECT '-9847.54' UNION ALL

SELECT '-1234.5784.54'

SELECT *

FROM @test-2

WHERE REPLACE(REPLACE(result_value, '.',''), '-','')

LIKE '%' + REPLICATE('[0-9]', LEN(REPLACE(REPLACE(result_value, '.',''), '-',''))) + '%' AND

ISNUMERIC(result_value) = 1

This seems to ferret out improperly formatted numbers as well as select the good ones. Not sure how this would perform with large numbers of records. You could also place a length limit into it as well, so as to avoid any number longer than some predefined number of digits.

Steve

(aka smunson)

:-):-):-)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)