Arithmetic overflow error - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Arithmetic overflow error

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources