Problem with POWER
-
I am getting an error message in an SQL statement that I don't understand This is the statement: UPDATE [QVSData].[dbo].[tbl_CentCatalogQVS] SET [Energy] = (POWER(10,(4.8+([Magnitude]*1.5))))/POWER(10,3) WHERE [AutoID] = 1 This is just a test hence restricted to 1 line to see if it works. It does not, and I am scratching my head as to why not. This is the error: Arithmetic overflow error for type int, value = 1995262314968882.700000 There is no int in the table. The affected fields here are: The Mag field is a decimal field (10,4) with in this instance a value of 7.0000 The energy field is a decimal field (20,1) Anyone have any ideas as to why this error is appearing? Actually I make that value 1995262314968.8 so it seems that it is possibly falling over as it calculates the first part before doing the division to get to Kilojoules. Why is it using int? To make it easier to figure out this error occurs if you run this: DECLARE @mag decimal(10,4) DECLARE @energy decimal(20,1) SET @mag = 7 SET @energy = (POWER(10,(4.8+(@mag*1.5))))/POWER(10,3) SELECT @energy The you get: Msg 232, Level 16, State 3, Line 5 Arithmetic overflow error for type int, value = 1995262314968882.700000. (1 row(s) affected) And the value is NULL Sorry forgot to add: SQL Server 2005 Developer Edition
-
I am getting an error message in an SQL statement that I don't understand This is the statement: UPDATE [QVSData].[dbo].[tbl_CentCatalogQVS] SET [Energy] = (POWER(10,(4.8+([Magnitude]*1.5))))/POWER(10,3) WHERE [AutoID] = 1 This is just a test hence restricted to 1 line to see if it works. It does not, and I am scratching my head as to why not. This is the error: Arithmetic overflow error for type int, value = 1995262314968882.700000 There is no int in the table. The affected fields here are: The Mag field is a decimal field (10,4) with in this instance a value of 7.0000 The energy field is a decimal field (20,1) Anyone have any ideas as to why this error is appearing? Actually I make that value 1995262314968.8 so it seems that it is possibly falling over as it calculates the first part before doing the division to get to Kilojoules. Why is it using int? To make it easier to figure out this error occurs if you run this: DECLARE @mag decimal(10,4) DECLARE @energy decimal(20,1) SET @mag = 7 SET @energy = (POWER(10,(4.8+(@mag*1.5))))/POWER(10,3) SELECT @energy The you get: Msg 232, Level 16, State 3, Line 5 Arithmetic overflow error for type int, value = 1995262314968882.700000. (1 row(s) affected) And the value is NULL Sorry forgot to add: SQL Server 2005 Developer Edition
-
-
I am getting an error message in an SQL statement that I don't understand This is the statement: UPDATE [QVSData].[dbo].[tbl_CentCatalogQVS] SET [Energy] = (POWER(10,(4.8+([Magnitude]*1.5))))/POWER(10,3) WHERE [AutoID] = 1 This is just a test hence restricted to 1 line to see if it works. It does not, and I am scratching my head as to why not. This is the error: Arithmetic overflow error for type int, value = 1995262314968882.700000 There is no int in the table. The affected fields here are: The Mag field is a decimal field (10,4) with in this instance a value of 7.0000 The energy field is a decimal field (20,1) Anyone have any ideas as to why this error is appearing? Actually I make that value 1995262314968.8 so it seems that it is possibly falling over as it calculates the first part before doing the division to get to Kilojoules. Why is it using int? To make it easier to figure out this error occurs if you run this: DECLARE @mag decimal(10,4) DECLARE @energy decimal(20,1) SET @mag = 7 SET @energy = (POWER(10,(4.8+(@mag*1.5))))/POWER(10,3) SELECT @energy The you get: Msg 232, Level 16, State 3, Line 5 Arithmetic overflow error for type int, value = 1995262314968882.700000. (1 row(s) affected) And the value is NULL Sorry forgot to add: SQL Server 2005 Developer Edition
Dividing by 10^3 is the same as subtracting 3 from 4.8+(@mag*1.5), so you should be able to avoid overflow if you rewrite your expression as follows:
POWER(10,(1.8+(@mag*1.5))). -
Dividing by 10^3 is the same as subtracting 3 from 4.8+(@mag*1.5), so you should be able to avoid overflow if you rewrite your expression as follows:
POWER(10,(1.8+(@mag*1.5))).Thank you. Seems a good scheme. I have absolutely no idea how you came up with that. Maths is not one of my strong points!
-
Thank you. Seems a good scheme. I have absolutely no idea how you came up with that. Maths is not one of my strong points!
TheComputerMan wrote:
I have absolutely no idea how you came up with that.
It's one of three main identities of exponentiation[^].
TheComputerMan wrote:
Maths is not one of my strong points!
Math is no longer required: you can type in your formula, and let a computer simplify it for you[^] :)
-
TheComputerMan wrote:
I have absolutely no idea how you came up with that.
It's one of three main identities of exponentiation[^].
TheComputerMan wrote:
Maths is not one of my strong points!
Math is no longer required: you can type in your formula, and let a computer simplify it for you[^] :)
We live and learn and live to learn another day! Thanks again. Once I have uncrossed by eyes I will see if I can assimilate any of that and become 1 of 10^666 Neat PDF that on Wolfram. Devilishly cunning stuff!