Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mdhingra01
Posting Yak Master
179 Posts |
 Posted - 2006-01-31 : 15:21:46
|
I get the follwoing error when attempting an insert into a table. Any ideas why?
Arithmetic overflow error converting numeric to data type numeric.
Warning: Null value is eliminated by an aggregate or other SET operation.
The statement has been terminated.
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-31 : 15:40:43
|
Well you have an overflow just as the error mentions. During conversion, you are overflowing the data type's range.
Tara Kizer
aka tduggan |
 |
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2006-01-31 : 15:41:10
|
Look in BOL at datatypes scales & precisions or give some more information (DDL & DML etc)
Eg:
SELECT CONVERT(numeric(9,2),1234567.89) = OK
SELECT CONVERT(numeric(8,2),1234567.89) = ERROR
Hope that helps
Andy
Beauty is in the eyes of the beerholder  |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2006-01-31 : 15:49:02
|
I am inserting into a table that I am creating on the fly.
Here is the SQL
Select program, Replace (erank,' ','')as erank, low, high,
COUNT (DISTINCT pos_NUMBER)as POSITIONS ,
cast
(round
(SUM(
(1.0/nb_empl)),0) as numeric) AS MONTHS,
Isnull
(cast
(round (
SUM
(CASE
WHEN LEFT(SURNAME,3)='***' THEN 1/nb_empl
END),0)as numeric),0) AS VAC,
STF=
cast
(round
(SUM(
(1.0/nb_empl)),0) as numeric)-
isnull
(cast
(round
(SUM
(CASE
WHEN LEFT(SURNAME,3)='***' THEN (1.0/nb_empl)
END),0)as numeric),0),
cast((LOW)*COUNT(CASE WHEN LEFT(SURNAME,3)!='***' THEN POS_NUMBER END) as numeric) AS STF_LOW,
cast((LOW)*COUNT(CASE WHEN LEFT(SURNAME,3)='***' THEN POS_NUMBER END)as numeric) AS VAC_LOW,
cast((HIGH)*COUNT(CASE WHEN LEFT(SURNAME,3)!='***' THEN POS_NUMBER END)as numeric) AS STF_HIGH,
cast((HIGH)*COUNT(CASE WHEN LEFT(SURNAME,3)='***' THEN POS_NUMBER END) as numeric) AS VAC_HIGH
into vac
from #vacPROOF
group by program, erank, low, high
order by program
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-31 : 15:51:27
|
You've got some data in one of the columns that you are casting that is outside of the range of the numeric data type. Please see Andy's post for an example. You'll need to provide a scale and precision at conversion to fit it in as the default isn't working for you.
Tara Kizer
aka tduggan |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2006-01-31 : 15:57:56
|
You are not defining the precision of the datatype, the default for numeric is 18
If your data violates that then you will need to handle/code it
SELECT CONVERT(numeric,1234567890123456789) = ERROR
SELECT CONVERT(numeric(19,0),1234567890123456789) = OK
Andy
Beauty is in the eyes of the beerholder |
|
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2006-01-31 : 15:59:03
|
Note to self - refresh page before posting 
Beauty is in the eyes of the beerholder |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2006-01-31 : 16:03:33
|
I tried removing te CAST
I still get the same error
Select program, Replace (erank,' ','')as erank, low, high,
COUNT (DISTINCT pos_NUMBER)as POSITIONS ,
round
(SUM(
(1.0/nb_empl)),0) AS MONTHS,
Isnull
(round ( SUM ( CASE
WHEN LEFT(SURNAME,3)='***' THEN 1/nb_empl
END),0),0) AS VAC,
STF=
round ( SUM ( ( 1.0/nb_empl ) ) , 0 )- isnull ( round ( SUM ( CASE WHEN LEFT(SURNAME,3)='***' THEN (1.0/nb_empl) END),0),0),
(LOW)*COUNT(CASE WHEN LEFT(SURNAME,3)!='***' THEN POS_NUMBER END) AS STF_LOW,
(LOW)*COUNT(CASE WHEN LEFT(SURNAME,3)='***' THEN POS_NUMBER END) AS VAC_LOW,
(HIGH)*COUNT(CASE WHEN LEFT(SURNAME,3)!='***' THEN POS_NUMBER END) AS STF_HIGH,
(HIGH)*COUNT(CASE WHEN LEFT(SURNAME,3)='***' THEN POS_NUMBER END) AS VAC_HIGH
into vac
from #vacPROOF
group by program, erank, low, high
order by program
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-01-31 : 16:05:19
|
You are getting the error on one of the calculations then. It is converting it implicitly for you even though you haven't put it in the code. Whichever calculation is failing, you'll need to convert it and provide a precision and scale to fix this.
Tara Kizer
aka tduggan |
|
|
|
|
|
|
|
|
Arithmetic overflow error