Arithmetic overflow error converting numeric to data type numeric

[alwayslrN]

With the following code

update nyl_sfp_remit
set net_rate =  case beg_prin_bal when 0 then 0 else (interest * 12/beg_prin_bal)*100 end
[/code/

I get the error mentioned in the subject line.

beg_prin_bal and interest are decimal(12,2) and net_rate is decimal(10,8).

the part of the code that reads
[code]
(interest * 12/beg_prin_bal)

yields a value like .06124984, I want it to return 6.12500000

Thanks for the help

 

[jmeckley]

I think you need to cast the whole expression using the new percision.

update nyl_sfp_remit
set net_rate =  convert(decimal(10,8), case beg_prin_bal when 0 then 0 else (interest * 12/beg_prin_bal)*100 end)

or

update nyl_sfp_remit
set net_rate =  case beg_prin_bal when 0 then 0 else convert(decimal(10,8), (interest * 12/beg_prin_bal)*100) end

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[alwayslrN]

Thanks Jason,

I had actually tried the latter and it did not work. Also tried your first option and it did not work either.

 

[alwayslrN]

Interesting, when I use the expression in a select statement I do not receive that error. It only happens when attempting to do an update.

 

[alwayslrN]

I think I figured out my problem. It seems the calculation yields a result of decimal(11,8) in some instances. Not good.

 

[ca8msm]

I'm sure there will be an easier way, but this returns the correct result:

SELECT ROUND(CAST(0.06124984 * 100 AS DECIMAL(11,8)), 3)

____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.