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

Question

Post reply

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

ashuthinks

SSCommitted

Points: 1698
More actions
May 9, 2012 at 8:39 am

#268629

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)
PLEASE HELP ME TO RESOLVE THIS ERROR.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

You have different numeric sizes in two different columns.

It is probably failing in the where clause.

What is the datatypes for bondTrade.TradeID and IRPTrade.TradeID? I am guessing these are both numeric but different scale/precision?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ashuthinks SSCommitted Points: 1698 More actions · Answer 2

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