SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Arithmetic overflow error converting nvarchar to data type numeric.


Arithmetic overflow error converting nvarchar to data type numeric.

Author
Message
duro654321
duro654321
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 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!
Gopi S
Gopi S
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 325
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
duro654321
duro654321
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 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))
Gopi S
Gopi S
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 325
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
duro654321
duro654321
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 20
i ran "update table set price = price * cast(1.1 as numeric(10,4))" and that worked pretty well
thanks! Smile
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25306 Visits: 12488
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

duro654321
duro654321
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 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?
duro654321
duro654321
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 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 ?
Gopi S
Gopi S
SSC Veteran
SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)SSC Veteran (295 reputation)

Group: General Forum Members
Points: 295 Visits: 325
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
Sean Lange
Sean Lange
SSC Guru
SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)SSC Guru (59K reputation)

Group: General Forum Members
Points: 59940 Visits: 17948
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.

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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search