I'm troubleshooting a stored procedure that suddenly decided to stop working. I narrowed down the problem to the last part of the stored procedure where it selects data from a temp table and inserts it into a physical table in the SQL2000 database.
I keep receiving the following error:
Server: Msg 8115, Level 16, State 8, Line 140
Arithmetic overflow error converting numeric to data type numeric.
The data values all appear to be correct with none of them seeming to be out of precision, but I keep getting the error. I've tried casting all the values and it didn't work. It executes w/o error when I comment out that particular insert. I just don't get it.
Any help would be appreciated. Thanks.
Code below:
-
declare @.dtAsOfdate DATETIME
set @.dtAsOfDate = '2006-04-16';
DECLARE @.RC INTEGER
-
-- 1) Eligible Investments:
-
-- Input: @.SPVId - SPV we are running process for
-- @.Yes - value of enum CCPEnum::eYesNoYes (get by lookup).
-- Output: Recordset (temp table) of Collaterals that are eligible for MV Test (#MVTriggerInvestments).
DECLARE @.Yes INTEGER
EXEC @.RC = [dbo].CPLookupVal 'YesNo', 'Yes', @.Yes OUTPUT
IF (@.RC<>0)BEGIN
RAISERROR ('SP_OCCalculationMVTriggerTest: Failed to find Yes enum', 16, 1) WITH SETERROR
END
drop table #MVTriggerInvestments
BEGIN
SELECT dbal.SPVId,
dbal.CusipId,
dbal.GroupId,
@.dtAsOfDate AS AsOfDate,
dbal.NormalOCRate,
dbal.SteppedUpOCRate,
dbal.AllocMarketValue AS MarketValue,
dbal.NbrDays,
dbal.PriceChangeRatio
INTO #MVTriggerInvestments
FROM DailyCollateralBalance dbal
JOIN CollateralGroupIncludeInOC gin
ON dbal.SPVId = 2
AND gin.SPVId = 2
AND dbal.AsOfDate = '2006-04-16'
AND @.dtAsOfDate BETWEEN gin.EffectiveFrom AND gin.EffectiveTo
AND dbal.GroupId = gin.GroupId
AND gin.IncludeInOC = @.Yes
END
select * from #MVTriggerInvestments
print 'end #1'
--select * from #MVTriggerInvestments --looks ok
--
-- 2) Calculate Weighted Average Price change ratio Market Value (by Group):
-- PCRMV - Price Change Ratio Market Value
--
-- Input : Recordset of collaterals (having New/Old prices, MarketValue defined)
-- Output: Recordset Aggregated by Group (#GroupOCRate)
drop table #MVTriggerGroup
BEGIN
SELECT A.SPVId,
A.AsOfDate,
A.GroupId,
A.NormalOCRate,
A.SteppedUpOCRate,
A.MarketValue,
cast([dbo].fn_divide_or_number (B.PriceChangeRatioMarketValue, B.MarketValueForPeriod, 0.00) as numeric(12,9)) as PriceChangeRatio,
CAST (0 AS NUMERIC(12,9)) AS OCRate,
CAST ('' AS VARCHAR(6)) AS OCRateType,
CAST (0 AS NUMERIC(18,2)) AS DiscMarketValue,
CAST (0 AS NUMERIC(18,2)) AS InterestAccrued
INTO #MVTriggerGroup
FROM
(
SELECT SPVId,
AsOfDate,
GroupId,
NormalOCRate,
SteppedUpOCRate,
cast(SUM(MarketValue) as numeric(18,2)) AS MarketValue
FROM #MVTriggerInvestments
GROUP BY SPVId, AsOfDate, GroupId, NormalOCRate, SteppedUpOCRate
) A --works up to here
JOIN
(SELECT SPVId,
cast(SUM(AllocMarketValue) as numeric(18,2)) AS MarketValueForPeriod ,
cast(SUM(AllocMarketValue * PriceChangeRatio) as numeric(18,2)) as PriceChangeRatioMarketValue,
GroupId
FROM T_DailyCollateralBalance
WHERE SPVId = 2
AND AsOfDate between '2006-03-17' and '2006-04-15'
AND IsBusinessDay = 1
GROUP BY SPVId, GroupId
) B
ON A.SPVId = B.SPVId
AND A.GroupId = B.GroupId
END
print 'end #2'
-- Calculate OCRate to apply for each group.
BEGIN
UPDATE #MVTriggerGroup
SET OCRate = (CASE WHEN ((PriceChangeRatio < 0) AND ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN SteppedUpOCRate
ELSE NormalOCRate
END),
OCRateType = (CASE WHEN ((PriceChangeRatio < 0) AND ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN 'stepup'
ELSE 'normal'
END)
END
print 'end #3'
-
-- Calculate discounted Market Value
-
UPDATE #MVTriggerGroup
SET DiscMarketValue = MarketValue / (1.0 + OCRate * 0.01)
print 'end #4'
-- Insert data from temp tables
-- 1)
select * from #MVTriggerInvestments
print 'begin tran'
BEGIN TRAN
DELETE T_MVTriggerInvestments
WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
print 'DELETE T_MVTriggerInvestments'
--error is here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
INSERT T_MVTriggerInvestments
(
SPVId ,
CusipId ,
GroupId ,
AsOfDate ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
NbrDays ,
OldPrice ,
NewPrice ,
PriceChangeRatio
)
SELECT SPVId ,
CusipId ,
GroupId ,
AsOfDate ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
NbrDays ,
0.00 ,
0.00 ,
PriceChangeRatio
FROM #MVTriggerInvestments
print 'end mvtriggerinv select'
COMMIT TRAN
--end error!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- 2)
print 'begin tran 2'
BEGIN TRAN
DELETE T_OCTestGroup
WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
INSERT T_OCTestGroup
(
SPVId ,
AsOfDate ,
GroupId ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
PriceChangeRatio,
OCRate ,
OCRateType ,
DiscMarketValue ,
InterestAccrued ,
SectionA ,
CPFace ,
IntExpense ,
Fees ,
SectionB ,
Receivables ,
IntReceivables ,
CashBalance ,
Investments ,
SectionC ,
ExcessCollateral,
MaxCPAllowed
)
SELECT
SPVId ,
AsOfDate ,
GroupId ,
NormalOCRate ,
SteppedUpOCRate ,
MarketValue ,
PriceChangeRatio,
OCRate ,
OCRateType ,
DiscMarketValue ,
InterestAccrued ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0 ,
0
FROM #MVTriggerGroup
print 'end tran 2'
COMMIT TRAN
Are the column definitions for the temp table the same for the table you're inserting into? Double-check the datatypes.|||Yes. I checked that and went through and tried casting any variables that might have been interpreted. I also checked the data and tried excluding certain records that had negative values (for example) and the error persists...
|||Maybe the permanent table has a computed column, or maybe there is a trigger on the table. Can you post the CREATE TABLE statements and the CREATE statements for any indexes, indexed views, or triggers for the tables involved? Steve Kass Drew University 7racer@.discussions.microsoft.com wrote:
> I'm troubleshooting a stored procedure that suddenly decided to stop
> working. I narrowed down the problem to the last part of the stored
> procedure where it selects data from a temp table and inserts it into a
> physical table in the SQL2000 database.
>
> I keep receiving the following error:
>
> Server: Msg 8115, Level 16, State 8, Line 140
> Arithmetic overflow error converting numeric to data type numeric.
>
> The data values all appear to be correct with none of them seeming to be
> out of precision, but I keep getting the error. I've tried casting all
> the values and it didn't work. It executes w/o error when I comment out
> that particular insert. I just don't get it.
>
> Any help would be appreciated. Thanks.
>
> Code below:
> -
>
> declare @.dtAsOfdate DATETIME
> set @.dtAsOfDate = '2006-04-16';
>
> DECLARE @.RC INTEGER
> -
> -- 1) Eligible Investments:
> -
>
> -- Input: @.SPVId - SPV we are running process for
> -- @.Yes - value of enum CCPEnum::eYesNoYes (get by
> lookup).
>
> -- Output: Recordset (temp table) of Collaterals that are eligible for
> MV Test (#MVTriggerInvestments).
>
> DECLARE @.Yes INTEGER
> EXEC @.RC = [dbo].CPLookupVal 'YesNo', 'Yes', @.Yes OUTPUT
> IF (@.RC<>0)BEGIN
> RAISERROR ('SP_OCCalculationMVTriggerTest: Failed to find Yes
> enum', 16, 1) WITH SETERROR
> END
> drop table #MVTriggerInvestments
> BEGIN
>
> SELECT dbal.SPVId,
> dbal.CusipId,
> dbal.GroupId,
> @.dtAsOfDate AS AsOfDate,
> dbal.NormalOCRate,
> dbal.SteppedUpOCRate,
> dbal.AllocMarketValue AS MarketValue,
> dbal.NbrDays,
> dbal.PriceChangeRatio
>
> INTO #MVTriggerInvestments
>
> FROM DailyCollateralBalance dbal
>
> JOIN CollateralGroupIncludeInOC gin
> ON dbal.SPVId = 2
> AND gin.SPVId = 2
> AND dbal.AsOfDate = '2006-04-16'
> AND @.dtAsOfDate BETWEEN gin.EffectiveFrom AND
> gin.EffectiveTo
> AND dbal.GroupId = gin.GroupId
> AND gin.IncludeInOC = @.Yes
>
> END
> select * from #MVTriggerInvestments
> print 'end #1'
> --select * from #MVTriggerInvestments --looks ok
>
> --
> -- 2) Calculate Weighted Average Price change ratio Market Value (by
> Group):
> -- PCRMV - Price Change Ratio Market Value
> --
>
> -- Input : Recordset of collaterals (having New/Old prices, MarketValue
> defined)
> -- Output: Recordset Aggregated by Group (#GroupOCRate)
> drop table #MVTriggerGroup
> BEGIN
>
> SELECT A.SPVId,
> A.AsOfDate,
> A.GroupId,
> A.NormalOCRate,
> A.SteppedUpOCRate,
> A.MarketValue,
>
> cast([dbo].fn_divide_or_number
> (B.PriceChangeRatioMarketValue, B.MarketValueForPeriod, 0.00) as
> numeric(12,9)) as PriceChangeRatio,
>
> CAST (0 AS NUMERIC(12,9))
> AS OCRate,
> CAST ('' AS VARCHAR(6))
> AS OCRateType,
> CAST (0 AS NUMERIC(18,2))
> AS DiscMarketValue,
> CAST (0 AS NUMERIC(18,2))
> AS InterestAccrued
>
> INTO #MVTriggerGroup
>
> FROM
> (
> SELECT SPVId,
> AsOfDate,
> GroupId,
> NormalOCRate,
> SteppedUpOCRate,
> cast(SUM(MarketValue) as numeric(18,2)) AS MarketValue
>
> FROM #MVTriggerInvestments
> GROUP BY SPVId, AsOfDate, GroupId, NormalOCRate,
> SteppedUpOCRate
> ) A --works up to here
>
> JOIN
> (SELECT SPVId,
> cast(SUM(AllocMarketValue) as numeric(18,2)) AS
> MarketValueForPeriod ,
> cast(SUM(AllocMarketValue * PriceChangeRatio) as
> numeric(18,2)) as PriceChangeRatioMarketValue,
> GroupId
>
> FROM T_DailyCollateralBalance
> WHERE SPVId = 2
> AND AsOfDate between '2006-03-17' and '2006-04-15'
> AND IsBusinessDay = 1
> GROUP BY SPVId, GroupId
> ) B
>
> ON A.SPVId = B.SPVId
> AND A.GroupId = B.GroupId
>
> END
> print 'end #2'
>
> -- Calculate OCRate to apply for each group.
>
> BEGIN
> UPDATE #MVTriggerGroup
> SET OCRate = (CASE WHEN ((PriceChangeRatio < 0) AND
> ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN SteppedUpOCRate
> ELSE NormalOCRate
> END),
> OCRateType = (CASE WHEN ((PriceChangeRatio < 0) AND
> ABS(PriceChangeRatio) > (0.55 * NormalOCRate)) THEN 'stepup'
> ELSE 'normal'
> END)
> END
> print 'end #3'
> -
> -- Calculate discounted Market Value
> -
> UPDATE #MVTriggerGroup
> SET DiscMarketValue = MarketValue / (1.0 + OCRate * 0.01)
> print 'end #4'
>
> -- Insert data from temp tables
>
> -- 1)
> select * from #MVTriggerInvestments
>
> print 'begin tran'
> BEGIN TRAN
> DELETE T_MVTriggerInvestments
> WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
> print 'DELETE T_MVTriggerInvestments'
> --error is
> here!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
> INSERT T_MVTriggerInvestments
> (
> SPVId ,
> CusipId ,
> GroupId ,
> AsOfDate ,
> NormalOCRate ,
> SteppedUpOCRate ,
> MarketValue ,
> NbrDays ,
> OldPrice ,
> NewPrice ,
> PriceChangeRatio
> )
> SELECT SPVId ,
> CusipId ,
> GroupId ,
> AsOfDate ,
> NormalOCRate ,
> SteppedUpOCRate ,
> MarketValue ,
> NbrDays ,
> 0.00 ,
> 0.00 ,
> PriceChangeRatio
>
> FROM #MVTriggerInvestments
> print 'end mvtriggerinv select'
> COMMIT TRAN
> --end
> error!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
> !!!!!
> -- 2)
> print 'begin tran 2'
> BEGIN TRAN
> DELETE T_OCTestGroup
> WHERE SPVId = 2 AND AsOfDate = '2006-04-16'
>
> INSERT T_OCTestGroup
> (
> SPVId ,
> AsOfDate ,
> GroupId ,
> NormalOCRate ,
> SteppedUpOCRate ,
> MarketValue ,
> PriceChangeRatio,
> OCRate ,
> OCRateType ,
> DiscMarketValue ,
> InterestAccrued ,
> SectionA ,
> CPFace ,
> IntExpense ,
> Fees ,
> SectionB ,
> Receivables ,
> IntReceivables ,
> CashBalance ,
> Investments ,
> SectionC ,
> ExcessCollateral,
> MaxCPAllowed
> )
> SELECT
> SPVId ,
> AsOfDate ,
> GroupId ,
> NormalOCRate ,
> SteppedUpOCRate ,
> MarketValue ,
> PriceChangeRatio,
> OCRate ,
> OCRateType ,
> DiscMarketValue ,
> InterestAccrued ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0 ,
> 0
>
> FROM #MVTriggerGroup
> print 'end tran 2'
> COMMIT TRAN
>
>
>
>
Joshua Kasper wrote:
Kayda_SQL wrote: