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:
>