November 24, 2008 at 10:28 am
I have an Arithmetic overflow error converting nvarchar to data type numeric.
My query looks like this:
select some_field = CASE WHEN isNUMERIC(table_field)=1 THEN CAST(table_field AS numeric(6)) ELSE NULL END
from My_Table
Now I have looked in the table and there is one record which has the letter 'E' instead of a number value in table_field. I don't simply want to manually replace this field as this is part of an ongoing automated process and I want the automation to catch the field on the fly and replace it. I find the error odd because if I run the same statement with a where clause looking specifically for a field with a non-numeric value it works as in the example below:
select some_field = CASE WHEN isNUMERIC(table_field)=1 THEN CAST(table_field AS numeric(6)) ELSE NULL END
from My_Table where isNumeric(table_field)=0
Any help greatly appreciated
November 24, 2008 at 10:49 am
Arithmetic overflow means you have a numeric value larger than 6 digits (since you're casting as Numeric(6)). The "E" isn't causing the problem in that one.
You can use the Len() function to find which rows are 1-million or larger. Just check for "Where len(table_field) > 6".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 26, 2008 at 9:05 am
Appreciate the help thanks -- I tried what you suggested and found the issue was a value in the field of 3,000,000. I changed the casting to make the field bigint but I am still having an error? Any thoughts?
December 2, 2008 at 2:56 pm
Do you mean you're casting to bigint instead of numeric(6) now? (3-million is inside the range for Int, you don't need to go quite as large as bigint for that.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 4, 2008 at 8:17 am
My apologies -- I have resolved this issue you were correct it didn't have anything to do with the 3 million value but to be honest I forget what I actually did to resolve the issue.
December 4, 2008 at 11:32 am
Glad I could help. Well done on fixing it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply