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

• Lowell

  SSC Guru

  Points: 323518

  More actions

  March 27, 2013 at 9:22 am

  #1601218

  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

  March 27, 2013 at 9:26 am

  #1601220

  Thanks for your quick reply.

  Both your options did not work..

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  March 27, 2013 at 11:12 am

  #1601272

  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

  March 27, 2013 at 11:51 am

  #1601294

  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.