Arithmetic overflow

Question

Post reply

Arithmetic overflow

Anchelin

SSCrazy

Points: 2417
More actions
March 12, 2007 at 3:12 pm

#369802

Hi All
I get the following error:
Arithmetic overflow error converting numeric to data type numeric.
my script is as follows:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tab]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tab]
GO
CREATE TABLE [dbo].[Tab] (
[col] [int] NOT NULL ,
[col] [datetime] NOT NULL ,
[col] [numeric](7, 1) NOT NULL ,
[col] [numeric](10, 0) NOT NULL ,
[col] [numeric](10, 0) NOT NULL ,
[col] [numeric](11, 0) NOT NULL ,
[col [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
When I try to insert values into the table it gives me the error above.
Can anyone help me with this one???
Thanks in advance.
Anchelin

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

well it loooks like you have your data type declarations incorrect.

you have declared a numeric (10,0)

which means your number can only be up to 1,000,000 with no decimals.

If any numbers your inserting have a decimal this will fail you need to use the proper scale.

Basically, the first number is the maximum number of digits your field will hold (Including decimals) and the scale is the maximum number of decimal places.

So 123456.789

would be Numberic (9,3)

From Books online, you

decimal[(p[, s])] and numeric[(p[, s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38.

s (scale)

Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

vadba SSChampion Points: 11133 More actions · Answer 2

Can you post the actual CREATE TABLE statement? The one you posted is invalid. All of the column names are the same, and the last column is missing a closing bracket - it is [col instead of [col].

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 3

I believe NUMERIC(10,0) will allow for 9,999,999,999, not just 1,000,000...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLBill SSC Guru Points: 51440 More actions · Answer 4

What values are you trying to enter that are giving the error? That's where you need to start.

-SQLBill

Anchelin SSCrazy Points: 2417 More actions · Answer 5

Hi All, thanks for all your replies!!

See code below:

CREATE on table [dbo].[TY27SBST](

[CUST_ACCT_HASH_NO] [int] NOT NULL,

[ANS_DT] [datetime] NOT NULL,

[ANS_TM] [numeric] (7,1) NOT NULL,

[UP_TRAFFIC] [numeric] (10,0) NOT NULL,

[DOWN_TRAFFIC] [numeric] (10,0) NOT NULL,

[TOTAL_TRAFFIC] [numeric](11,0) NOT NULL,

[IMSI][char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_UMSDTY27] PRIMARY KEY CLUSTERED (

[CUST_ACCT_HASH_NO] ASC,

[ANS_DT] ASC,

[ANS_TM] ASC,

[IMSI] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

CREATE on table [dbo].[TY27SBST](

[CUST_ACCT_HASH_NO] [int] NOT NULL,

[ANS_DT] [datetime] NOT NULL,

[ANS_TM] [numeric] (7,1) NOT NULL,

[UP_TRAFFIC] [numeric] (10,0) NOT NULL,

[DOWN_TRAFFIC] [numeric] (10,0) NOT NULL,

[TOTAL_TRAFFIC] [numeric](11,0) NOT NULL,

[IMSI][char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_UMSDTY27] PRIMARY KEY CLUSTERED (

[CUST_ACCT_HASH_NO] ASC,

[ANS_DT] ASC,

[ANS_TM] ASC,

[IMSI] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

This is what I want to insert:

INSERT INTO UMSSERVER02.UMS_REPORTS.DBO.TY27SBST

(CUST_ACCT_HASH_NO,ANS_DT,ANS_TM,UP_TRAFFIC,DOWN_TRAFFIC,TOTAL_TRAFFIC,IMSI)

VALUES (475448700,'2007-02-28',225900.0,4426498588,9702545870094336,35192832,'786356315648521')

Thanks in advance

Anchelin

vadba SSChampion Points: 11133 More actions · Answer 6

The value you are trying to insert for column DOWN_TRAFFIC is way too big. DOWN_TRAFFIC is declared as numeric(10,0) and the value you are inserting is 16 digits ( 9702545870094336 ) !

INSERT INTO DBO.TY27SBST

(CUST_ACCT_HASH_NO, ANS_DT, ANS_TM, UP_TRAFFIC, DOWN_TRAFFIC, TOTAL_TRAFFIC, IMSI)

VALUES (475448700,'2007-02-28',225900.0,4426498588,9702545870094336,35192832,'786356315648521')

SQLBill SSC Guru Points: 51440 More actions · Answer 7

Same with the last value....it's more than 11 digits.

-SQLBill

Anchelin SSCrazy Points: 2417 More actions · Answer 8

Hi All

Thanks for all your input... I altered my table and everything seems to be fine now.

How can I ever repay you guys?

Thanks again...

Anchelin