Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ssdeveloper
Starting Member
37 Posts |
 Posted - 2012-08-02 : 17:10:44
|
| Hi all,I am getting this error (Arithmetic overflow error converting float to data type numeric.), when I try to run this in a select stmt:CONVERT(decimal(20,10),(aa.amount * CONVERT(decimal(20,10),cc.bal_rate,2)) /SUM(aa.amount * cc.balance_rate) OVER(PARTITION BY aa.contact_id)Out of this, balance_rate is of float datatype. Any way we can get rid of this error? and also, How can we determie which record is causing this?thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-02 : 17:14:45
|
| can you try giving a high precision valueie likeDecimal(35,10)also are you sure you need a scale upto 10?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
ssdeveloper
Starting Member
37 Posts |
Posted - 2012-08-02 : 17:24:31
|
| I already did give the high precision value like (26,10) and it worked, but I am not sure if that ruins any other things?And I am unable to pin down to what record is causing this! Any suggestions ont that?This is sensitive to the firm, so I just don't want to overcome the issue by increasing the precision, do you think it might do something bad?Thanks so much by the way for the timely response |
|
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-08-02 : 17:49:23
|
| The decimal(20,10) means you get 10 digits to the left of the decimal and 10 to the right. Either cc.bal_rate has a value >= 10000000000 or cc.bal_rate * amount has a value >= 10000000000. JimEveryday I learn something that somebody else already knew |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2012-08-02 : 18:39:03
|
| This might help to answer what SQL does under the covers with regaurd to how it changes the scale and precision of numeric data types and why increasing the precision or scale might cause issues:http://msdn.microsoft.com/en-us/library/ms190476.aspx |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-02 : 21:35:42
|
quote:
Originally posted by ssdeveloper
I already did give the high precision value like (26,10) and it worked, but I am not sure if that ruins any other things?And I am unable to pin down to what record is causing this! Any suggestions ont that?This is sensitive to the firm, so I just don't want to overcome the issue by increasing the precision, do you think it might do something bad?Thanks so much by the way for the timely response
its because the current way you've declared it causes it to be unable to store value returned by expression as its integral part is going over 10 digits.so unless you change it you wont be able to accomodate calculated result------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Error for Datatype Conversion