asked
Problem with power function
Respected geeks , i am getting error when trying to execute this query query is this select A= ISNULL(45000.00,0.00)+(ISNULL(45000.00,0.00)*(cast((POWER((cast((1.00-(1.01)) as float)),(cast(-(isnull(23.00,0.00)-1) as float)))) as decimal(18,2)))/0.01); here variable that is holding 23.00 is decimal(18,2) here variable that is holding 45000.00 is decimal(18,2) A is declared decimal(24,2)
Hi there. Running your code the error returned is: Msg 8115, Level 16, State 6, Line 1 Arithmetic overflow error converting float to data type numeric. Now, looking only at what you are trying to convert to numeric (or [decimal, but they are functionally equivalent data types][1]) is: SELECT A = POWER(( CAST(( 1.00 - ( 1.01 ) ) AS FLOAT) ), ( CAST(-( ISNULL(23.00, 0.00) - 1 ) AS FLOAT) )) Now, when we run this we get 9.99999999999999E+43. This is a very large number which will overflow the decimal(18,2) you tried to cast it to (indeed, would overflow any decimal type). Valid numbers for the decimal type are [10^38 +1 through 10^38 - 1][2]. You are effectively running : SELECT POWER(CONVERT(FLOAT,-0.01),CONVERT(FLOAT,-22)) This asks for -0.01 to the power -22, which is indeed the aforementioned very large number. I'm not sure that you are using POWER itself incorrectly - but I do think that either you are trying to use the wrong datatypes for your data, or there is something wrong with the way you are structuring your query, and you aren't expecting to be asking the question that is in fact fed to the SQL server. Can you clarify what you're trying to achieve, so that we can better help you? [1]:
http://technet.microsoft.com/en-us/library/ms187746.aspx [2]:
http://technet.microsoft.com/en-us/library/ms187746.aspx
i want to use this select POWER((1.00-(1.01)) ,-(isnull(80.00,0.00)-1.00) ) when i use float it works fine but may i know how can i convert output of power function to decimal(18,2) ?
As Dave said, you can't put a number into decimal(18,2) that doesn't fit. Put it this way, how do you want 9.99999999999999E+43 to be shown as a decimal(18,2)?
But that's an entirely different number? If that's how you want to show it, then you'd have to treat the value as a character string and manipulate it that way : `select left(convert(varchar(100),(POWER(cast(-0.01 as float) ,-22.00 )),2),4)`
sorry my dear now i am stuck in this problem please get me out from this please declare @CorpR decimal(18,2) select @CorpR= ISNULL(45000.00,0.00)+(ISNULL(45000.00,0.00)*(convert(decimal(18,2), left(convert(varchar(100),(POWER(cast((1.00-(1.01)) as float) ,(-(isnull(45.00,0.00)-1)) )),2),4))/0.01) print '@CorpR' now its showing irritating error please help me ..
No you can't use my string manipulation solution to then turn the value back into a number - it's a completely different number! Perhaps we should start again. What is it you are trying to do? And by that I don't mean show me the code that isn't working, but what is the actual requirement?
No you can't use my string manipulation to then turn the value into a number - it's the wrong value! What is it you are trying to achieve?
Large numbers make me nervous but isn't 9.99999999999999E+43 just about the same thing as 99999999999999900000000000000000000000000000 ? I can see how 9.99 wouldn't quite do it justice in the same way that just using 1.11 wouldn't do justice to 11111111111111100000000000000000000000000000
------------------ this is my whole procedure CREATE PROC [dbo].[InsUpdCusRetPlanning] @CsId int , @luby int, @IpAdr varchar(25) as Begin DECLARE @DetailedErrorDesc VARCHAR(MAX) begin try begin tran tr declare @YtR decimal(18,2),@RetAg decimal(18,2),@Monreq decimal(18,2),@YafR decimal(18,2),@InfRtNeed decimal(18,2), @CorpR decimal(24,2), @AlcFund decimal(18,2),@BalCorp decimal(18,2),@LumpInv decimal(18,2), @SamPMT money,@YrlInvGrwthShown money, @Rtrn decimal(18,2),@ExpRetAstId tinyint,@curAge decimal(9,2) ,@Infla decimal(9,2),@NetRtn decimal(18,2) /**/ declare @curentdate datetime select @curentdate= dbo.getdateist(1) declare @datetocompare datetime select @datetocompare= isnull(dob,'01/01/1990') from CustDtls where csid=@CsId and memid=1 print '@curentdate - 'print @curentdate print '@datetocompare - 'print @datetocompare If exists ( Select 1 from tblcusret where Cusid=@CsId) begin select @curAge=[dbo].[getdatediffinYrs](@datetocompare,@curentdate) select @YtR=YeatiRe, @RetAg=RetAg,@Monreq=ISNULL(Monreq,0.00) from tblcusret where Cusid=@CsId print '@YtR before - 'print @YtR select @YtR= @RetAg - @curAge print '@@RetAg - 'print @RetAg print '@curAge - ' print @curAge print '@YtR after - 'print @YtR print '@datetocompare - ' print @datetocompare if(@YtR<0 or @YtR is null) begin raiserror('Years to Retire is negative',16,127) return commit tran tr end set @YafR=90-@RetAg; select @Infla=isnull(RtnVal,0.00) from CsBPAsmptn where LkpTyp='ASTAsm' and AssetId=3 print '@Infla' print @Infla Select @InfRtNeed=(dbo.GetFV(isnull(@Monreq,0.00),@YtR,@Infla))*12 print '@InfRtNeed' print @InfRtNeed --((isnull(@Monreq,0.00)*power(((100+(select isnull(RtnVal,0.00) from CsBPAsmptn where LkpTyp='ASTAsm' and AssetId=3))/100),@YtR))*12) select @CorpR= ISNULL(@InfRtNeed,0.00)+(ISNULL(@InfRtNeed,0.00)*(POWER((cast((1.00-(1.01)) as decimal(18,2))),(cast(-(isnull(@YafR,0.00)-1) as decimal(18,2)))))/0.01); print '@CorpR' print @CorpR select @ExpRetAstId= CASE WHEN @YtR < 1 THEN 12 WHEN @YtR < 2 THEN 4 WHEN @YtR < 3 THEN 5 WHEN @YtR < 4 THEN 6 WHEN @YtR < 5 THEN 7 WHEN @YtR < 7 THEN 8 WHEN @YtR < 10 THEN 9 WHEN @YtR = 10 THEN 10 WHEN @YtR > 10 THEN 11 END select @Rtrn=isnull(RtnVal,0.0000) from csbpAsmptn where LkpTyp='ASTAsm' and AssetId=@ExpRetAstId; print '@Rtrn' print @Rtrn select @NetRtn=isnull(NetRet,0.00) from CsBPFnlAsset where CsId=@CsId select @AlcFund = (isnull(@Monreq,0.0000)*(power(((100+@NetRtn)/100.00),@YtR))); set @BalCorp=@CorpR-ISNULL(@AlcFund,0.0000); print '@BalCorp' print @BalCorp select @LumpInv=dbo.GetPV( isnull(@BalCorp,0.0000),isnull(@YtR,0.00),@Rtrn); print '@LumpInv' print @LumpInv select @SamPMT=dbo.GetPMTBeginMode(isnull(@BalCorp,0.00),isnull(@YtR,0.00),isnull(@Rtrn,0.00)); print '@SamPMT' print @SamPMT select @YrlInvGrwthShown=dbo.GetPMTwithGrowth(100.00,ISNULL(@YtR,0.00),10.00,isnull(@Rtrn,0.00),isnull(@BalCorp,0.00)) print '@YrlInvGrwthShown' print @YrlInvGrwthShown update tblcusret set Infadrene=@InfRtNeed, coratret=@CorpR, Allfu=@AlcFund, valallfe=@AlcFund, Balcor=@BalCorp, Luminv=@LumpInv, SumPm=@SamPMT, Yeainwiqr=null, YeainwiqrSh=@YrlInvGrwthShown, ludt=dbo.getdateist(0), luby=@luby, ip=@IpAdr where Cusid=@CsId end commit tran end try begin catch Rollback tran tr SELECT @DetailedErrorDesc = CAST(ERROR_NUMBER() AS VARCHAR) + ' : '+ CAST(ERROR_SEVERITY() AS VARCHAR) + ' : ' + CAST(ERROR_STATE() AS VARCHAR) + ' : ' + ERROR_PROCEDURE() + ' : ' + ERROR_MESSAGE() + ' : ' + CAST(ERROR_LINE() AS VARCHAR); raiserror(@DetailedErrorDesc,16,127) end catch End
17 People are following this question.
