January 10, 2008 at 9:07 am
I have a table called tbExtract and the money field is varchar(50).
Some values are nulls and others are populated. So I have tried using the case when to check if its numeric then attempt the convert but its keeps giving me the same error ? How can I find the
records that are causing this error? They are more than 10 million rows.
INSERT tbTEST
SELECT
CASE
WHEN ISNUMERIC(AMOUNT) = 1 THEN CONVERT(DECIMAL(18,3),AMOUNT)
ELSE NULL
END AS AMT
FROM tbExtract
Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
January 10, 2008 at 9:14 am
There's probably an elegant way, but I'd write a cursor to print each value, then when the error occurs, look for the next value.
January 10, 2008 at 9:31 am
>>Arithmetic overflow error converting numeric to data type numeric.
The data type is already numeric, so it's not a type conversion due to anything that IsNumerci() will help you with.
You've got a value that is larger than will fit in numeric(18,3).
[font="Courier New"]
Select cast(999999999999999.999 as numeric(18,3))
Success
Select cast(9999999999999999.999 as numeric(18,3))
Server: Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
[/font]
Modify your CASE statement to handle large values outside of the range of numeric(18,3)
January 10, 2008 at 9:39 am
Try this one on for size - use the new-fangled TRY/CATCH to do this.
Cut it into chunks and run the operation on a chunk at a time.
This presumes you have some field to allow you to "walk the table"
--This code will return the first "chunk" with a problem in it
declare @dummyconvert int --this would be your variable - type it appropriately
declare @chunksize int --this helps narrow the results down to something manageable
declare @maxid int
declare @currID int -- what we use to "walk" the table
select @maxid=max(IDent) from matric2
select @chunksize=100,@currid=1
Begin Try
WHILE (@currID<@maxid)
Begin
--run the test to a dummy variable just to get the error to happen
select
@dummyconvert=convert(INT, matricul)
from
matric2
where
IDent between @currID and @currID+@chunksize
Select @currid=@currID+@chunksize+1
END
End Try
Begin Catch
Select 'A problem occurs in this block',error_number(),error_message()
Select *
from
matric2
where
IDent between @currID and @currID+@chunksize
End Catch
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 11, 2008 at 1:20 am
I modified my script and when I run it, it blocks itself ...
--Create Rownum to allow "walking through the table"
ALTER TABLE tbExtract ADD Rownum INT IDENTITY(1,1)
--This code will return the first "chunk" with a problem in it
DECLARE @Acct_NoConvert DECIMAL(18,3)
--Narrow the results down to a manageable Chunk
DECLARE @ChunksizeINT
DECLARE @MaxidINT
-- ID to "walk" the table
DECLARE @CurrIDINT
SELECT @Maxid=Max(Rownum) FROM tbExtract
SELECT @Chunksize = 100,@Currid = 1
BEGIN TRY
WHILE (@CurrID < @Maxid)
BEGIN
--run the test to a dummy variable just to get the error to happen
SELECT
@Acct_NoConvert = CONVERT(DECIMAL(18,3),AMOUNT)
FROM
tbExtract
WHERE
Rownum BETWEEN @CurrID AND @CurrID + @Chunksize
SELECT @Currid = @CurrID + @Chunksize + 1
END
END TRY
BEGIN CATCH
SELECT 'A problem occurs in this block',error_number(),error_message()
SELECT *
FROM
tbExtract
WHERE
Rownum BETWEEN @CurrID and @CurrID + @Chunksize
END CATCH
January 11, 2008 at 6:55 am
Why dont you use a hint (ReadUncommitted) in the Catch part to prevent blocking?
-Roy
January 11, 2008 at 6:56 am
What exactly happens?
Put an index covering the ID and amount, so that you're not hammering the table with scans.
Something like:
Create index ix_tblExtract_ID on tblExtract(ID) include (amount)
Otherwise - just put in some details as to what you're seeing.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 11, 2008 at 7:18 am
THANKS...I WILL REMEMBER THAT NEXT TIME. I ENDED UP INCREASING MY AMOUNT COLUMN FROM DECIMAL(13,3) TO
DECIMAL(18,3) AND IT RAN SUCESSFULLY.
THANKS A LOT
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply