Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Arithmetic overflow error converting nvarchar to data type numeric. Expand / Collapse
Author
Message
Posted Saturday, October 13, 2012 10:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 9:21 PM
Points: 6, Visits: 20
hello

when i run the following sql query:

"update table set price = price * 1.1 "

i get the following error :
"Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting nvarchar to data type numeric.
The statement has been terminated."

the table is set to nvarchar, and i am just trying to make the prices go up 10%
any ideas?
thanks!
Post #1372436
Posted Saturday, October 13, 2012 12:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:01 PM
Points: 157, Visits: 286
Hi,
In the expression [price * 1.1], SQL Server tries to implicit convert price into numeric(2,1) because 1.1 is type numeric(2,1) which has higher precedence than nvarchar. I think your table should have values greater that 10.
say 10.1, 10.2...... where the precision value is exceeding 2.

To resolve this try
update table set price = price * cast(1.1 as numeric(10,4))

Note: If your price still has more that 10 precision value then change it accordingly.

Thanks
Gopi
Post #1372445
Posted Saturday, October 13, 2012 12:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 9:21 PM
Points: 6, Visits: 20
the value of the price field is:
.05
.25
.6
1
1.25
150.7
250
265.75
etc

so if there are no items price that are greater than $1000 then i would use the following: ?

update field set price = price * cast(1.1 as numeric(1000,7))
Post #1372448
Posted Saturday, October 13, 2012 12:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:01 PM
Points: 157, Visits: 286
no. '1000' is precision 4. so you can have numeric(4,0). ie 4 decimal values in the left of decimal point and not numeric(1000,7)

Ref: http://msdn.microsoft.com/en-us/library/ms187746.aspx

You can understand what is numeric data type and what is precision and scale number

Post #1372452
Posted Saturday, October 13, 2012 12:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 9:21 PM
Points: 6, Visits: 20
i ran "update table set price = price * cast(1.1 as numeric(10,4))" and that worked pretty well
thanks! :)
Post #1372455
Posted Saturday, October 13, 2012 1:20 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:00 AM
Points: 8,551, Visits: 9,043
Gopi S (10/13/2012)
Hi,
In the expression [price * 1.1], SQL Server tries to implicit convert price into numeric(2,1) because 1.1 is type numeric(2,1) which has higher precedence than nvarchar. I think your table should have values greater that 10.
say 10.1, 10.2...... where the precision value is exceeding 2.

To resolve this try
update table set price = price * cast(1.1 as numeric(10,4))

Note: If your price still has more that 10 precision value then change it accordingly.

Thanks
Gopi

Yes, it's the implicit conversion that's going wrong because the default type for 1.2 is numeric(2,1). But there's another problem: if you try to increase a price like 10.37 by 10%, just multiplying by 1.1 doesn't work because it will deliver 11.387 which may not be a valid currency amount (eg if the currency is euro the smallest value is .01, so .007 is invalid) and you have to decide what rounding you want to do in this case - perhaps rounding to nearest valid number (and what ever you want to do to round something ending in .xy5) or perhaps rounding down or perhaps some other rule.
So I think I would go for something like
update table 
set price = convert(nvarchar(36), cast(cast(price as numeric(35,2))*1.1 as numeric(35,2)))

(the inner cast should solve the error; the outer cast does simple default rounding, so replace it with something that does whatever sort or rounding you need if the default isn't what you want)

Of course if you have a currency unit that splits into 1000 parts instead of 100, you need to use scale 3 instead of scale 2, and so on for other splits. If you will sometimes want to multiply by something more complicated than 1.1 (for example by 1.0625, for a 6¼% increase) you need to reduce the precision from 35 to avoid the possibility of the multiply operation doing some rounding instead of whatever rounding you have chosen to do - pushing it down to 32 isn't going to put much of a constraint on your prices, unless you have prices of 1000000000000000000000000000000.00 currency units or more. In fact you could just look and see what your nvarchar price column is declared as: if it's nvarchar(N) you should use precision N-1, unless whoever specified it was being sloppy.


Tom
Post #1372460
Posted Sunday, October 14, 2012 1:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 9:21 PM
Points: 6, Visits: 20
thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

that worked, it cleaned up the data from 5.50000000 to 5.5

but now when i run "select top 10 * from table order by price "

it shows me
x 9.9
x 9.9

instead of 10 +

??

any ideas?
Post #1372484
Posted Sunday, October 14, 2012 2:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 19, 2012 9:21 PM
Points: 6, Visits: 20
when i run

select * from table order by price asc

i get...

115.50
36.00
4.95
4.95
4.95
47.30
49.50
49.50
53.90

when i should get

4.95
4.95
4.95
36.00
47.30
49.50
49.50
53.90
115.50



what do i need to change in the sql query ?
Post #1372489
Posted Sunday, October 14, 2012 5:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:01 PM
Points: 157, Visits: 286
You are trying to order the varchar column and not numeric though the value looks numeric.
Try

--Query 1
select
price
from table
order by CAST(price as numeric(10,4)) asc

--Query 2
select price from (
select CAST(price as numeric(10,4))
price
from table) T
order by price asc

Again this is just hint. Your cast function should have appropriate precision and scale based on previous post
Post #1372497
Posted Monday, October 15, 2012 7:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 13,067, Visits: 11,903
Or even better, change the column type so you don't have to continually fight the fact that you are storing numbers in a character column.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1372735
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse