Showing posts with label caused. Show all posts
Showing posts with label caused. Show all posts

Monday, February 13, 2012

Arithmetic Overflow

I'm getting an "Arithmetic Overflow" error in the "LAST STEP" section in the
DDL below. It's caused when I try to "CAST" a decimal and varchar field into
decimal type.
The reason behind these strange conversions is that within the production
environment, the data that is being imported into the #tmpTempTable is
coming from a legacy FoxPro database that was poorly designed.
If someone can just help me make the "LAST STEP" section work and "CAST" the
fields without error, I will be able to build an access front-end for this
data.
DDL **********
IF object_id('tempdb..#tmpTempTable') IS NOT NULL
BEGIN
DROP TABLE #tmpTempTable
END
CREATE TABLE #tmpTempTable (
tll_grpfk char(5) NOT NULL,
tll_meanpeak decimal(5, 1) NULL,
tll_i_meanpeak varchar(15) NULL,
tll_npieces int NULL
)
GO
insert into #tmpTempTable (tll_grpfk, tll_meanpeak, tll_i_meanpeak,
tll_npieces) values
('00001', '26.2', '1188855', '45435')
insert into #tmpTempTable (tll_grpfk, tll_meanpeak, tll_i_meanpeak,
tll_npieces) values
('00002', '15.5', '9703464', '625518')
IF object_id('tblData') IS NOT NULL
BEGIN
DROP TABLE tblData
END
CREATE TABLE tblData (
tll_grpfk int NOT NULL,
tll_meanpeak decimal(5, 1) NULL,
tll_i_meanpeak decimal(8, 1) NULL,
tll_npieces int NULL
)
GO
-- LAST STEP
insert into tblData (tll_grpfk, tll_meanpeak, tll_i_meanpeak, tll_npieces)
select CAST(tll_grpfk AS INT), CAST(tll_meanpeak as decimal(5, 1)),
CAST(tll_i_meanpeak as decimal(5, 1)), tll_npieces
from #tmpTempTableYou can't cast 1188855 or 9703464 as decimal(8,1).
Looks like you typed a 5 where you meant to type 8
in CAST(tll_i_meanpeak as decimal(5, 1))
Steve Kass
Drew University
scott wrote:

>I'm getting an "Arithmetic Overflow" error in the "LAST STEP" section in th
e
>DDL below. It's caused when I try to "CAST" a decimal and varchar field int
o
>decimal type.
>The reason behind these strange conversions is that within the production
>environment, the data that is being imported into the #tmpTempTable is
>coming from a legacy FoxPro database that was poorly designed.
>If someone can just help me make the "LAST STEP" section work and "CAST" th
e
>fields without error, I will be able to build an access front-end for this
>data.
>
>DDL **********
>IF object_id('tempdb..#tmpTempTable') IS NOT NULL
>BEGIN
> DROP TABLE #tmpTempTable
>END
>CREATE TABLE #tmpTempTable (
> tll_grpfk char(5) NOT NULL,
> tll_meanpeak decimal(5, 1) NULL,
> tll_i_meanpeak varchar(15) NULL,
> tll_npieces int NULL
> )
>GO
>insert into #tmpTempTable (tll_grpfk, tll_meanpeak, tll_i_meanpeak,
>tll_npieces) values
> ('00001', '26.2', '1188855', '45435')
>insert into #tmpTempTable (tll_grpfk, tll_meanpeak, tll_i_meanpeak,
>tll_npieces) values
> ('00002', '15.5', '9703464', '625518')
>
>IF object_id('tblData') IS NOT NULL
>BEGIN
> DROP TABLE tblData
>END
>CREATE TABLE tblData (
> tll_grpfk int NOT NULL,
> tll_meanpeak decimal(5, 1) NULL,
> tll_i_meanpeak decimal(8, 1) NULL,
> tll_npieces int NULL
> )
>GO
>-- LAST STEP
>insert into tblData (tll_grpfk, tll_meanpeak, tll_i_meanpeak, tll_npieces)
>select CAST(tll_grpfk AS INT), CAST(tll_meanpeak as decimal(5, 1)),
> CAST(tll_i_meanpeak as decimal(5, 1)), tll_npieces
>from #tmpTempTable
>
>
>|||what can i cast or convert it to in order to be decimal type?
"Steve Kass" <skass@.drew.edu> wrote in message
news:e5kMajCZGHA.1220@.TK2MSFTNGP02.phx.gbl...
> You can't cast 1188855 or 9703464 as decimal(8,1).
> Looks like you typed a 5 where you meant to type 8
> in CAST(tll_i_meanpeak as decimal(5, 1))
> Steve Kass
> Drew University
> scott wrote:
>