Type Mismatch when Adding Matching Type Decimal Fields 
Author Message
 Type Mismatch when Adding Matching Type Decimal Fields

Okay, I'm having a really basic problem that I just can't believe could
actually a bug.  My source table has two decimal columns both defined
Precision=7, Scale=2 and my target table has one decimal column also with
Precision=7, Scale=2.  The following line fails on a "Type Mismatch" error
during a simple ActiveX transformation using VBScript:

     DTSDestination("Total_Amount") = DTSSource("Amount_1") +
DTSSource("Amount_2")

I'm simply adding two decimal fields -- this is not a complex operation
here.  I even changed the target to Precision=9, Scale=2 to acommodate a
larger decimal, but it didn't help.

I'm running SQL Server 2000 with SP2.  Any ideas?  Thanks very much.

Chuck



Fri, 31 Dec 2004 20:31:22 GMT
 Type Mismatch when Adding Matching Type Decimal Fields

Chuck,

Do you have error checking for NULL values in the source columns and error
checking for overflow conditions?

--

-Steve Jansen
Byte Interactive


Quote:
> Okay, I'm having a really basic problem that I just can't believe could
> actually a bug.  My source table has two decimal columns both defined
> Precision=7, Scale=2 and my target table has one decimal column also with
> Precision=7, Scale=2.  The following line fails on a "Type Mismatch" error
> during a simple ActiveX transformation using VBScript:

>      DTSDestination("Total_Amount") = DTSSource("Amount_1") +
> DTSSource("Amount_2")

> I'm simply adding two decimal fields -- this is not a complex operation
> here.  I even changed the target to Precision=9, Scale=2 to acommodate a
> larger decimal, but it didn't help.

> I'm running SQL Server 2000 with SP2.  Any ideas?  Thanks very much.

> Chuck



Fri, 31 Dec 2004 23:59:38 GMT
 Type Mismatch when Adding Matching Type Decimal Fields
I don't, but in a previous step I convert all NULLS to zero.  Also, incoming
fields are defined decimal(7,2) so should not overflow -- expecially after I
changed target field to decimal(9.2)

Since this is a dollar field, I changed it to SMALLMONEY from DECIMAL(7,2)
and this solved the problem.  SMALLMONEY is defined as DECIMAL(10,4) so I
imagine I might have achieved the same results with regular DECIMAL(10,4)
though I didn't try it.  I don't know why a money field has 4 places to the
right of the decimal, but I guess I'll just have to deal with that.  Thanks
for your reply.
Chuck


Quote:
> Chuck,

> Do you have error checking for NULL values in the source columns and error
> checking for overflow conditions?

> --

> -Steve Jansen
> Byte Interactive



> > Okay, I'm having a really basic problem that I just can't believe could
> > actually a bug.  My source table has two decimal columns both defined
> > Precision=7, Scale=2 and my target table has one decimal column also
with
> > Precision=7, Scale=2.  The following line fails on a "Type Mismatch"
error
> > during a simple ActiveX transformation using VBScript:

> >      DTSDestination("Total_Amount") = DTSSource("Amount_1") +
> > DTSSource("Amount_2")

> > I'm simply adding two decimal fields -- this is not a complex operation
> > here.  I even changed the target to Precision=9, Scale=2 to acommodate a
> > larger decimal, but it didn't help.

> > I'm running SQL Server 2000 with SP2.  Any ideas?  Thanks very much.

> > Chuck



Sat, 01 Jan 2005 01:37:03 GMT
 Type Mismatch when Adding Matching Type Decimal Fields
Chuck,

According to BOL, the MONEY type I would guess that the MONEY data type as
having " accuracy to a ten-thousandth of a monetary unit."  Thus, the scale
of 4 is required to give accuracy to 0.001 monetary units.

Undoubtedly, this accuracy is of high importance to those performing complex
financial calculations (e.g., stock prices), and to those
displaying/computing financial information in monetary units other than US
dollars (e.g, Yen, Pounds, Francs, etc) -- remember SQL Server supports
cultural locales other than US English.

-Steve Jansen
Byte Interactive


Quote:
> I don't, but in a previous step I convert all NULLS to zero.  Also,
incoming
> fields are defined decimal(7,2) so should not overflow -- expecially after
I
> changed target field to decimal(9.2)

> Since this is a dollar field, I changed it to SMALLMONEY from DECIMAL(7,2)
> and this solved the problem.  SMALLMONEY is defined as DECIMAL(10,4) so I
> imagine I might have achieved the same results with regular DECIMAL(10,4)
> though I didn't try it.  I don't know why a money field has 4 places to
the
> right of the decimal, but I guess I'll just have to deal with that.
Thanks
> for your reply.
> Chuck



> > Chuck,

> > Do you have error checking for NULL values in the source columns and
error
> > checking for overflow conditions?

> > --

> > -Steve Jansen
> > Byte Interactive



> > > Okay, I'm having a really basic problem that I just can't believe
could
> > > actually a bug.  My source table has two decimal columns both defined
> > > Precision=7, Scale=2 and my target table has one decimal column also
> with
> > > Precision=7, Scale=2.  The following line fails on a "Type Mismatch"
> error
> > > during a simple ActiveX transformation using VBScript:

> > >      DTSDestination("Total_Amount") = DTSSource("Amount_1") +
> > > DTSSource("Amount_2")

> > > I'm simply adding two decimal fields -- this is not a complex
operation
> > > here.  I even changed the target to Precision=9, Scale=2 to acommodate
a
> > > larger decimal, but it didn't help.

> > > I'm running SQL Server 2000 with SP2.  Any ideas?  Thanks very much.

> > > Chuck



Sat, 01 Jan 2005 01:58:57 GMT
 Type Mismatch when Adding Matching Type Decimal Fields
Thanks for the info!

Quote:
> Chuck,

> According to BOL, the MONEY type I would guess that the MONEY data type as
> having " accuracy to a ten-thousandth of a monetary unit."  Thus, the
scale
> of 4 is required to give accuracy to 0.001 monetary units.

> Undoubtedly, this accuracy is of high importance to those performing
complex
> financial calculations (e.g., stock prices), and to those
> displaying/computing financial information in monetary units other than US
> dollars (e.g, Yen, Pounds, Francs, etc) -- remember SQL Server supports
> cultural locales other than US English.

> -Steve Jansen
> Byte Interactive



> > I don't, but in a previous step I convert all NULLS to zero.  Also,
> incoming
> > fields are defined decimal(7,2) so should not overflow -- expecially
after
> I
> > changed target field to decimal(9.2)

> > Since this is a dollar field, I changed it to SMALLMONEY from
DECIMAL(7,2)
> > and this solved the problem.  SMALLMONEY is defined as DECIMAL(10,4) so
I
> > imagine I might have achieved the same results with regular
DECIMAL(10,4)
> > though I didn't try it.  I don't know why a money field has 4 places to
> the
> > right of the decimal, but I guess I'll just have to deal with that.
> Thanks
> > for your reply.
> > Chuck


message

> > > Chuck,

> > > Do you have error checking for NULL values in the source columns and
> error
> > > checking for overflow conditions?

> > > --

> > > -Steve Jansen
> > > Byte Interactive



> > > > Okay, I'm having a really basic problem that I just can't believe
> could
> > > > actually a bug.  My source table has two decimal columns both
defined
> > > > Precision=7, Scale=2 and my target table has one decimal column also
> > with
> > > > Precision=7, Scale=2.  The following line fails on a "Type Mismatch"
> > error
> > > > during a simple ActiveX transformation using VBScript:

> > > >      DTSDestination("Total_Amount") = DTSSource("Amount_1") +
> > > > DTSSource("Amount_2")

> > > > I'm simply adding two decimal fields -- this is not a complex
> operation
> > > > here.  I even changed the target to Precision=9, Scale=2 to
acommodate
> a
> > > > larger decimal, but it didn't help.

> > > > I'm running SQL Server 2000 with SP2.  Any ideas?  Thanks very much.

> > > > Chuck



Sat, 01 Jan 2005 04:46:37 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Return type mismatch when using MFC ODBC class with decimal type

2. To add a field of the type Dates with a field of the Whole type

3. Type Mismatch while using Activex Script Task with numeric data type

4. type mismatch error while importing data to a BIT type column

5. Data Types in CreateParameter statement causing type mismatch

6. Type Mismatch reading from Access 2K of type Date

7. Advanced Queuing: ORA-25215 user_data type and queue type do not match

8. Data Type SQL Server7.0 need a non decimal 1 E 19 data type

9. Type mismatch txtBox <- sql-memo field

10. DBAPI - Oracle mismatching field types

11. Type Mismatch on a bound field

12. Type Mismatch on a bound field


 
Powered by phpBB® Forum Software