# question

## 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)

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

·
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

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 ·
bhanupratapsngh9 ·
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 ·