Arithmetic overflow error converting varchar to data type numeric.

Question

Post reply

Arithmetic overflow error converting varchar to data type numeric.

rmany

SSC Enthusiast

Points: 111
More actions
March 27, 2013 at 9:18 am

#291586

Here is my query,
select a.row,sum(CONVERT(decimal(11,0), a.value)) as total
from
(SELECT adc.row,adc.col,adc.value FROM AnesthesiaDrugCells adc
right join AnesthesiaDrugs ad on adc.row = ad.row
where adc.ProcID=11080 and ad.ProcID = 11080 AND adc.row>=2 and adc.row<=11
and adc.value != '' and ad.transmissiontype != 'I' and adc.value !='-' and (ISNUMERIC(adc.value)=1)) as a
where(ISNUMERIC(a.value)=1)
group by a.row
order by a.row
Resulting in error message 'Arithmetic overflow error converting varchar to data type numeric.'
If I just execute,
(SELECT adc.row,adc.col,adc.value FROM AnesthesiaDrugCells adc
right join AnesthesiaDrugs ad on adc.row = ad.row
where adc.ProcID=11080 and ad.ProcID = 11080 AND adc.row>=2 and adc.row<=11
and adc.value != '' and ad.transmissiontype != 'I' and adc.value !='-' and (ISNUMERIC(adc.value)=1))
I am getting,
rowcolvalue
7150
511
1054
81150
8250
61100
6450
4135
Now I need to sum all those values for each row.
My result should be like
row value
4 35
5 1
6 200 so on..
'm not sure where am I going wrong. Please someone help.
Thank you

Viewing 5 posts - 1 through 5 (of 5 total)

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

Lowell SSC Guru Points: 323755 More actions · Answer 1

change this to a larger number:

SUM(CONVERT(DECIMAL(11, 0), a.value)) AS total

something liek this:

SUM(CONVERT(DECIMAL(19, 4), a.value)) AS total

or

SUM(CONVERT(float, a.value)) AS total

does it work then?

you keep bumping into the limits of your data type.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rmany SSC Enthusiast Points: 111 More actions · Answer 2

Thanks for your quick reply.

Both your options did not work..

Lynn Pettis SSC Guru Points: 442470 More actions · Answer 3

You have fallen into a common trap. Just because ISNUMERIC(adc.value) = 1 is true does not mean that convert(decimal(11,2), adc.value) will work.

Please read the following article: http://www.sqlservercentral.com/articles/IsNumeric/71512/.

rmany SSC Enthusiast Points: 111 More actions · Answer 4

Lynn Pettis (3/27/2013)
You have fallen into a common trap. Just because ISNUMERIC(adc.value) = 1 is true does not mean that convert(decimal(11,2), adc.value) will work.
Please read the following article: http://www.sqlservercentral.com/articles/IsNumeric/71512/.

Thanks a lot. That worked.