October 13, 2012 at 10:58 am
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!
October 13, 2012 at 12:01 pm
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
October 13, 2012 at 12:27 pm
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))
October 13, 2012 at 12:39 pm
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
October 13, 2012 at 12:51 pm
i ran "update table set price = price * cast(1.1 as numeric(10,4))" and that worked pretty well
thanks! 🙂
October 13, 2012 at 1:20 pm
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
October 14, 2012 at 1:31 am
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?
October 14, 2012 at 2:46 am
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 ?
October 14, 2012 at 5:39 am
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
October 15, 2012 at 7:51 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 26, 2015 at 12:18 pm
Thanks for this post - it helped me solve a similar problem.
But, wouldn't it be more clear to use:
update table set price = cast(price as numeric(10,4)) * 1.1
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply