Link to home
Create AccountLog in
Avatar of ckangas7
ckangas7

asked on

Problem with Converting Data in Insert Statement

Am trying to insert data from a table (t_insert_emp_detail with all raw varchar data into another table (t_employee_detail) where all the data is to be formatted correctly.   I am hanging up on this part of the insert:

insert into t_employee_detail (emp_special_2)
select
CASE isnumeric (f14)
when 1 Then
CAST (f14 as decimal(9,6))
ELSE 0.00
END
from t_insert_emp_detail

I get the error:
Arithmetic overflow error converting numeric to data type numeric.

However if I run just a select statement on t_insert_emp_detail:

select f14,
CASE isnumeric (f14)
when 1 Then
CAST (f14 as decimal(9,6))
ELSE 0.00
END as converted
from t_insert_emp_detail

Then it converts everthing without errors and in comparing the raw data to the converted data they look identical.

The datatype of the emp_special_2 field is decimal (9,6), the raw data (imported from a delimeted text file) is varchar(50).  

Any idea what I am doing wrong?  I got this to work on smaller set of data so it is probably faulty data somewhere but if that is the case why do I not get an error on the select statement?



Avatar of chapmandew
chapmandew
Flag of United States of America image

does this work

insert into t_employee_detail (emp_special_2)
cast(select
CASE isnumeric (f14)
when 1 Then
CAST (f14 as decimal(9,6))
ELSE 0.00
END as numeric)
from t_insert_emp_detail
Avatar of ckangas7
ckangas7

ASKER

Nope,  I get:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'CAST'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'from'.

Sorry...try this instead

insert into t_employee_detail (emp_special_2)
select
cast(CASE isnumeric (f14)
when 1 Then
CAST (f14 as decimal(9,6))
ELSE 0.00
END as numeric)
from t_insert_emp_detail
I  get the same initial error:

Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
Ok....what is the data type of the field you are trying to insert into?
decimal (9,6)
What value(s) are returned when you run this:

select f14,
CASE isnumeric (f14)
when 1 Then
CAST (f14 as decimal(9,6))
ELSE 0.00
END as converted
from t_insert_emp_detail
It runs no problem.  I get an f14 column that shows the raw number data (ie 10.833321) and another column 'converted' that shows the same data (ie 10.833321).  It seems there is no problem with the conversion.  The problem arises when I try to insert the data into the other table.
Ok, so if that field is decimal(9,6) then this should work:

insert into t_employee_detail (emp_special_2)
select
cast(CASE isnumeric (f14)
when 1 Then
CAST (f14 as decimal(9,6))
ELSE 0.00
END as decimal(9,6)))
from t_insert_emp_detail
Still getting the arithmetic overflow error.  If I run just the Select part of your statement there is no issue, but when I add the insert line I get the error.  Also I had to remove one of the ')' on the next to last line.
I am starting to think that your table you are inserting into isn't decimal(9,6))  I just inserted that value into a field w/ that data type and it worked fine for me.
I attached a screen shot of my table structure.
screenshot.bmp
So, you are only inserting that single value in that row?
No there are about 3000 rows of data in this case.  I am also inserting other values into other columns in a bigger script that is also failing.  I have isolated the error down to this one statement though.  Thought I would save you the headache of trying to sort through the whole thing.
I have a feeling that your data is hosing you.....
Thats what I think, but it makes no sense to me that the select cast statement works fine by itself.  Usually that will tell you if you have bad data.  Anyway, browsing through the data I can't see anything but numbers.  Can you think of a way for me to identify the bad data?
ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I was hoping it would be something easy : - ).  I will try breaking it up into sections using where clauses and see what happens.  I will let you know.  
Ok I found the culprit.  The bad data is 719.985600 .  Shouldn't that work with decimal (9,6).  The way I understand it is that the 9 means 9 digits and the 6 is the number of decimal places.  Yes?
That is how it is supposed to work....
Try it this way:

insert t_employee_detail (emp_special_2)
Select
      CASE ISNUMERIC(f14)
            WHEN 1 THEN
                  CASE
                        WHEN CAST(f14 as int) >= 1000 THEN 999.999999
                        WHEN CAST(f14 as int) <= -1000 THEN -999.999999
                        ELSE CAST(f14 as decimal(9,6)))
                  END
            ELSE 0.00
      END
from t_insert_emp_detail

This will at least show the problem data.

P.S. I would not be surprised if you do not have some data with exponential numbers in them.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Look for data in t_insert_emp_detail where Len(f14) > 15
or where substr(F14,'.') > 9
JimFive,
Not sure if you realize, but the Precision is the total number of digits including the ones to the right of the decimal point.  In other words you cannot assign a number greater than 999.999999 to a data type defined as decimal(9, 6)
@acperkins
You're right of course.
That was it.  On closer examination the bad bad number was actually 719.9985600.   I changed the  data type to decimal (10,6) and all went well.  Thanks much for your help guys.