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 Kizeraka 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) = OKSELECT CONVERT(numeric(8,2),1234567.89) = ERRORHope that helpsAndyBeauty 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 SQLSelect 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_HIGHinto vacfrom #vacPROOFgroup by program, erank, low, highorder 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 Kizeraka 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 18If your data violates that then you will need to handle/code itSELECT CONVERT(numeric,1234567890123456789) = ERRORSELECT CONVERT(numeric(19,0),1234567890123456789) = OKAndyBeauty 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 CASTI still get the same errorSelect 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_HIGHinto vacfrom #vacPROOFgroup by program, erank, low, highorder 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 Kizeraka tduggan |
|
|
|
|
|
|
|