Arithmetic overflow error - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Arithmetic overflow error

Author

Topic

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-06-13 : 19:16:14

I am trying to run this query:

SELECT 'D', property.parcel_number, ROUND(.23 + property_char.value * .02731, 2) AS nwcharge
FROM
property INNER JOIN
property_char ON property.id = property_char.property_id INNER JOIN property_char AS pc ON property.id = pc.property_id INNER JOIN
prop_valuation ON property.id = prop_valuation.property_id INNER JOIN
val_component ON property.id = val_component.property_id INNER JOIN
val_component AS vc ON property.id = vc.property_id

but I am getting the error:

"Arithmetic overflow error converting varchar to data type numeric".

It seems to be cause by the line: ROUND(.23 + property_char.value * .02731, 2) AS nwcharge.

Please help.

Thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-06-13 : 19:38:34

What is the data type of the column property_char.value? Can you determine the value of property_char.value that is causing the overflow to occur?

=======================================
We should try to be the parents of our future rather than the offspring of our past. -Miguel de Unamuno

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-14 : 00:46:27

quote:
Originally posted by nguyenl

I am trying to run this query:

SELECT 'D', property.parcel_number, ROUND(.23 + property_char.value * .02731, 2) AS nwcharge
FROM
property INNER JOIN
property_char ON property.id = property_char.property_id INNER JOIN property_char AS pc ON property.id = pc.property_id INNER JOIN
prop_valuation ON property.id = prop_valuation.property_id INNER JOIN
val_component ON property.id = val_component.property_id INNER JOIN
val_component AS vc ON property.id = vc.property_id

but I am getting the error:

"Arithmetic overflow error converting varchar to data type numeric".

It seems to be cause by the line: ROUND(.23 + property_char.value * .02731, 2) AS nwcharge.

Please help.

Thanks

It seems like you've some nonnumeric characters in property_char.value field. Try like this to see if you've some invalid numeric values coming in field

SELECT value FROM property_char where ISNUMERIC(value)=0

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-14 : 11:46:31

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/enhanced-isnumeric-function.aspx

Madhivanan

Failing to plan is Planning to fail

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-06-16 : 14:45:37

The Data type for "Value" is Varchar(100). After I ran this query: SELECT value FROM property_char where ISNUMERIC(value)=0, I got 54 results. I updated these 54 results by putting in zeroes. However, when I ran my query, I get the same error.

nguyenl
Posting Yak Master

128 Posts

Posted - 2008-06-16 : 16:45:22

I also just finished running madhivanan suggestion. I didn't find any "non-numerics".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources