Data type problem

Arithmetic overflow error converting numeric to data type numeric.
I'm getting this error when I'm trying to insert.
this is my SQL
insert into ABSCTRL
( ID, column1 )
values ('123',12)
ID is char (4)
Column1 is decimal(4,1) in the database table.
Please let me know why I'm getting this error.
Thanks
ChandraAre you quite certain you got the data types right?
ML
http://milambda.blogspot.com/|||Can you post the table script?|||yes..I was right about the data types. Why my insert statement is not workin
g'
"ML" wrote:

> Are you quite certain you got the data types right?
>
> ML
> --
> http://milambda.blogspot.com/|||Hi Chandra,
If your datatype is right, then the problem is with the value you are
trying to insert.
If your column1 datatype is decimal(4,1)
then the value you will have to insert should be between -1000 and 1000
(limits excluded). If not it will give an error that you mentioned.
Can you post the table create script and the insert that you tried to perfor
m.|||There's something else going on. Below does not return any errors:
CREATE TABLE ABSCTRL
(ID char (4),
Column1 decimal(4,1) )
insert into ABSCTRL
( ID, column1 )
values ('123',12)
SELECT * FROM ABSCTRL
If the table definition and the INSERT corresponds to your envoronment, then
it could be an inert
trigger on the ABSCTRL table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:A77E1CD0-9FB7-417B-B926-1DE794713ABD@.microsoft.com...
> Arithmetic overflow error converting numeric to data type numeric.
> I'm getting this error when I'm trying to insert.
> this is my SQL
> insert into ABSCTRL
> ( ID, column1 )
> values ('123',12)
> ID is char (4)
> Column1 is decimal(4,1) in the database table.
> Please let me know why I'm getting this error.
>
> Thanks
> Chandra
>|||This is my SQL Script
CREATE TABLE [dbo].[ABSCTRL] (
[ID] [char] (4) NOT NULL ,
[column1] [decimal](1, 0) NOT NULL ,
) ON [PRIMARY]
GO
I don't know why I'm getting [decimal](1, 0) because in design I gave
length as 4 and precision as 1. Pleaselet me know where I made a mistake.
"Tibor Karaszi" wrote:

> There's something else going on. Below does not return any errors:
> CREATE TABLE ABSCTRL
> (ID char (4),
> Column1 decimal(4,1) )
>
> insert into ABSCTRL
> ( ID, column1 )
> values ('123',12)
> SELECT * FROM ABSCTRL
>
> If the table definition and the INSERT corresponds to your envoronment, th
en it could be an inert
> trigger on the ABSCTRL table.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:A77E1CD0-9FB7-417B-B926-1DE794713ABD@.microsoft.com...
>
>|||you should have given the precision as 4 and scale as 1
you had declared it wrong. Change it and try it.|||Thanks a lot.. it's working!!!
"Omnibuzz" wrote:

> you should have given the precision as 4 and scale as 1
> you had declared it wrong. Change it and try it.

Data Type in Oracle is not translating in SSIS datatype

My OLDB data source in the Lookup Transformation is not converting

When I try to join the colunms it claims the data types do not match the only differnece is the percision.?

ORACLE
select CAST(group_id AS NUMBER(18)) group_id from blah blah table
SSIS data type = [DT_NUMERIC], Percision = 38

SQL Sever data source
SELECT
CAST(PRINCIPAL_ID as numeric) GROUP_ID,
SSIS data type = [DT_NUMERIC], Percision = 18The metadata of the columns being joined should have to match exactly. (same precision, same

scale)

Data type conversion problem

Hi,
I have a variety of fields that I am trying to convert from Float to Decimal
for greater accuracy however we are having some problems converting them.
We use this fantastic tool called "SQL compare" by Red-Gate software except
that it's failing in one spot. So, we are now working with the remaining
fields that need to be converted field-by-field.
When we try to convert the field called dpst_amt it fails with:
Msg 8115, level 16, State 6, line 1
Arithmetic overflow error converting float to data type numeric.
Statment terminated
The values in [dpst_amt] field are numbers like 693.399999999998, if I try
[select round(dpst_amt,2)] the value never is rounded, if I try to copy the
value out to X field it fails as well. How did I determine what is in these
fields, meaning how do I find the bad record? One must be bad but where or
how do I find it?
Thank you!
ChrisWhat is the size of your decimal datatype? There must be some data in your
column that is larger than the largest that fits in the decimal datatype, so
if your decimal is for example decimal(6,2)
SELECT dpst_amt
FROM your_table
WHERE ABS(dpst_amt) > 9999.99
should give you the values that are outside the range of your decimal.
SELECT MAX(ABS(dps_amt)) FROM your_table
will give you an indication as to what the size of your decimal datatype
should be.
Jacco Schalkwijk
SQL Server MVP
"Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
news:ueaB9$xWFHA.3876@.tk2msftngp13.phx.gbl...
> Hi,
> I have a variety of fields that I am trying to convert from Float to
> Decimal for greater accuracy however we are having some problems
> converting them. We use this fantastic tool called "SQL compare" by
> Red-Gate software except that it's failing in one spot. So, we are now
> working with the remaining fields that need to be converted
> field-by-field.
> When we try to convert the field called dpst_amt it fails with:
> Msg 8115, level 16, State 6, line 1
> Arithmetic overflow error converting float to data type numeric.
> Statment terminated
> The values in [dpst_amt] field are numbers like 693.399999999998, if I try
> [select round(dpst_amt,2)] the value never is rounded, if I try to copy
> the value out to X field it fails as well. How did I determine what is in
> these fields, meaning how do I find the bad record? One must be bad but
> where or how do I find it?
> Thank you!
> Chris
>|||Originally the datatype was Float and what I've found since writing this was
the value of [1.#INF] was stored in the field. How does one search for that
value in the future?
Thank you!
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eukytqyWFHA.2912@.TK2MSFTNGP10.phx.gbl...
> What is the size of your decimal datatype? There must be some data in your
> column that is larger than the largest that fits in the decimal datatype,
> so if your decimal is for example decimal(6,2)
> SELECT dpst_amt
> FROM your_table
> WHERE ABS(dpst_amt) > 9999.99
> should give you the values that are outside the range of your decimal.
> SELECT MAX(ABS(dps_amt)) FROM your_table
> will give you an indication as to what the size of your decimal datatype
> should be.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Chris Marsh" <cmarsh@.synergy-intl.com> wrote in message
> news:ueaB9$xWFHA.3876@.tk2msftngp13.phx.gbl...
>