why getting error :Arithmetic overflow error converting numeric to data type numeric

Question

SELECT 
    TradeId,
    Trade_SecurityId,
    SecurityType.*, 
    Trade_ParAmount Quantity 
INTO 
    #Securities 
FROM 
    Fireball.dbo.PreAssignSecurityType SecurityType
INNER JOIN 
    Fireball_RawImportData.dbo.Import_WSO_TradeReport TradeReport ON 
        SecurityType.NativeTradeId = TradeReport.Trade_ID AND
        TradeReport.Trade_TradeDate = SecurityType.TradeDate
INNER JOIN
    Fireball..Trade ON
        Trade.NativeTradeId = SecurityType.NativeTradeID
WHERE 
    SecurityType.TradeDate = '2012-02-02'

after that

INSERT INTO 
    Fireball..IRPTrade 
    (TradeId, Par, TradeFee, AccruedInterest, AccruedPIK, AccruedFees)
SELECT 
    TradeId, 
    Par, 
    TradeFee, 
    AccruedInterest, 
    AccruedPIK, 
    AccruedFees 
FROM 
    Fireball..bondTrade 
WHERE 
    TradeId IN 
    (
    SELECT 
        TradeId 
    FROM 
        #Securities 
    WHERE 
        SecurityType = 'IRP' OR 
        SecurityType = 'IRS'
    ) AND
    NOT EXISTS(
        SELECT  *
        FROM -- GETTING ERROR AT THIS LINE WHY :(
        Fireball..IRPTrade
    WHERE
        TradeId = bondTrade.TradeId)

Table definitions:

BondTradeId int Unchecked
TradeId int Unchecked
Par decimal(32, 4)  Checked
TradeFee    decimal(32, 4)  Checked
AccruedInterest decimal(32, 4)  Checked
AccruedPIK  decimal(32, 4)  Checked
AccruedFees decimal(32, 4)  Checked
        Unchecked

IRPTradeId  int Unchecked
TradeId int Unchecked
Par decimal(32, 4)  Checked
TradeFee    decimal(32, 4)  Checked
AccruedInterest decimal(32, 4)  Checked
AccruedPIK  decimal(32, 4)  Checked
AccruedFees decimal(32, 4)  Checked
        Unchecked

Might be one of the column value get exceeds decimal(32,4) from table Fireball..IRPTrade , Fireball..bondTrade?

Arithmetic overflow error converting numeric to data type numeric — Neo
– Neo, Commented May 9, 2012 at 14:47
can you post the datatypes of the columns in the tables near the error? Or post the table defs for each table? — Taryn
– Taryn, Commented May 9, 2012 at 14:55
OK i will update other coulmns are decimal(32,4) may be value gets exceeds bcoz of that getting error? — Neo
– Neo, Commented May 9, 2012 at 15:05
@Siva I have updated my question with datatypes plz help me. — Neo
– Neo, Commented May 9, 2012 at 15:11

Patrick Garceau · Accepted Answer · 2012-05-10 02:47:27Z

1

Last time I got a similar error was because one of the value in a numeric field was set to NaN, not a number, corrected the value or deleted the row, was fine after that.

answered May 10, 2012 at 2:47

Patrick Garceau

511 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

JackoBongo · Accepted Answer · 2012-05-09 14:46:31Z

1

Without the definitions of the table it's hard to answer :) Based on the error message it's most likely an error raised during the INSERT because data from a VARCHAR column cannot be correctly converted to an INT column.

answered May 9, 2012 at 14:46

JackoBongo

873 silver badges12 bronze badges

Collectives™ on Stack Overflow

why getting error :Arithmetic overflow error converting numeric to data type numeric

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related