|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Help with Arithmetic Overflow errorprocedure. The SP was developed some time ago and recently started thowing this error: Arithmetic overflow error converting numeric to data type numeric. This error doesn't seem to make much sense since it happens when inserting data into a physical table from a temp table in the stored procedure. I've been pulling my hair out trying to figure out a way to fix it. I'm pasting the code below along with my print statement and comment showing where the error occurs. Any help is GREATLY appreciated. Thanks! --ALTER PROCEDURE [dbo].SP_OCCalculationMVTriggerTest 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, [dbo].fn_divide_or_number (B.PriceChangeRatioMarketValue, B.MarketValueForPeriod, 0.00) 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, SUM(MarketValue) AS MarketValue FROM #MVTriggerInvestments GROUP BY SPVId, AsOfDate, GroupId, NormalOCRate, SteppedUpOCRate ) A --works up to here JOIN (SELECT SPVId, SUM(AllocMarketValue) AS MarketValueForPeriod, SUM(AllocMarketValue * PriceChangeRatio) 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 This is what the data looks like:
2 3128HDZR3 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 .00 956 .15469 2 3128JLT66 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 22764923.64 693 .40260 2 3128JLVQ9 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 33325930.88 693 .12272 2 3128JMY50 ARM 3-1 2006-04-16 00:00:00.000 4.220000000 6.330000000 .00 480 -.37161 2 3128NCB86 ARM 3-1 2006-04-16 00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000 2 3128NCNV2 ARM 3-1 2006-04-16 00:00:00.000 4.220000000 6.330000000 .00 19 .00000 2 31295KSK1 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 .00 956 -.10075 2 31295NBU1 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 .00 956 .97381 2 31295NCW6 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 .00 956 .23484 2 31295NDA3 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 3445254.54 956 .51738 2 31295NMK1 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 .00 956 .18800 2 31295NNJ3 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 4723304.57 956 -.06034 2 31295NUE6 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 10974246.83 956 -.33549 2 31295NUG1 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 .00 956 .09947 2 31295NUR7 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 3840958.96 956 -.41991 2 31295NZ76 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 4679164.24 956 -.24881 2 31390YZH4 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 3333555.16 956 -.11334 2 31402C6D9 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 46830311.23 689 .74464 2 31402DB25 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 .00 689 .27197 2 31402DBZ2 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 .00 689 .78266 2 31404EXL5 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 7785530.05 721 -.22302 2 31404LPV6 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 7814559.22 755 .72999 2 31404MBN7 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 .00 721 .03317 2 31405JJ21 ARM 3-1 2006-04-16 00:00:00.000 4.220000000 6.330000000 .00 480 -.26118 2 31409ARA9 ARM 3-1 2006-04-16 00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000 2 31409BB71 ARM 3-1 2006-04-16 00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000 2 31409BGD3 ARM 5-1 2006-04-16 00:00:00.000 5.140000000 7.710000000 .00 13 3083.50000 2 31409DSH7 ARM 3-1 2006-04-16 00:00:00.000 4.220000000 6.330000000 .00 13 3083.50000 2 31409DSM6 ARM 3-1 2006-04-16 00:00:00.000 4.220000000 6.330000000 .00 7 3083.50000 2 31409DVN0 ARM 5-1 2006-04-16 00:00:00.000 5.140000000 7.710000000 .00 13 3083.50000 2 31409UYN9 ARM 5-1 2006-04-16 00:00:00.000 5.140000000 7.710000000 .00 13 3083.50000 2 36225CWV4 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 1575644.70 956 -.14477 2 36225DAY0 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 8995207.86 693 -.17400 2 31337NND6 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 .00 956 .00290 2 31349SEA7 ARM 3-1 2006-04-16 00:00:00.000 4.220000000 6.330000000 26777158.30 875 -.32595 2 31349SHH9 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 19822156.78 840 -.22582 2 31387A3M5 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 .00 956 .50317 2 31391BKL0 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 4267353.02 956 -.98672 2 31391CJA4 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 7446628.34 956 -1.00060 2 31391NM65 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 2783766.24 956 -.33597 2 31404E2E5 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 23112826.15 689 -.16876 2 31404EEJ1 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 12078241.02 784 .10121 2 31404JRP2 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 16699637.14 755 .28834 2 31404JVR3 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 10781379.91 755 .43313 2 31404JWZ4 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 11040810.52 662 .46652 2 31404JXW0 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 3883162.72 721 .27610 2 31404JXZ3 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 .06 662 .00905 2 31404KRS3 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 .00 693 -.20539 2 36225C4Y9 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 .00 784 -.04323 2 36225C6Y7 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 .00 721 -.13550 2 36225C7B6 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 19935755.89 721 -.15476 2 36225DAV6 ARM 1-1 FI 2006-04-16 00:00:00.000 2.930000000 4.395000000 11457579.74 693 -.15476 Wish I could upload a spreadsheet...  |
|||||||||||||||||||||||
Other interesting topics