question

bhanupratapsngh9 avatar image
bhanupratapsngh9 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)
t-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dave_Green avatar image
Dave_Green answered
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
9 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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) ?
0 Likes 0 ·
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)?
0 Likes 0 ·
i want to put it like 9.99 only
0 Likes 0 ·
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)`
0 Likes 0 ·
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 ..
0 Likes 0 ·
this is table CREATE TABLE [dbo].[tblCusRet]( [Retid] [int] NOT NULL, [Cusid] [int] NOT NULL, [RetAg] [tinyint] NOT NULL, [Monreq] [money] NOT NULL, [Assid] [tinyint] NULL, [AssAmt] [money] NULL, [YeatiRe] [tinyint] NULL, [YeaafRe] [tinyint] NULL, [Infadrene] [money] NULL, [coratret] [money] NULL, [Allfu] [money] NULL, [valallfe] [money] NULL, [Balcor] [money] NULL, [Luminv] [money] NULL, [SumPm] [money] NULL, [Yeainwiqr] [money] NULL, [YeainwiqrSh] [money] NULL, [credt] [datetime] NULL, [ludt] [datetime] NULL, [crby] [int] NULL, [luby] [int] NULL, [ip] [varchar](30) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
0 Likes 0 ·
Show more comments
bhanupratapsngh9 avatar image
bhanupratapsngh9 answered
------------------ 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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.