2

I am attempting to generate a table containing values of 10^(existing_field_value / 10). I can recreate the problem with the following simplified table:

Date,       Time,     Timer,   Overall_Spectra, 6_3_Hz, 20_Hz
10/23/2018, 12:24:13, 0:19:59, 69.7,            -17.4,  8.9
10/23/2018, 12:24:14, 0:19:58, 70.8,            -31.1,  4.4
10/23/2018, 12:24:15, 0:19:57, 70.7,            -28.9,  4.8
10/23/2018, 12:24:16, 0:19:56, 69.0,            -27.0,  5.9

The data was imported from a flat text file with the following data types:

Date, date
Time, time(0)
Timer, time(0)
Overall_Spectra, decimal(3,1)
6_3_Hz, decimal(3,1)
20_Hz, decimal(3,1)

Using the T-SQL statement below, I was able to comment out 2 of the 3 referenced fields at a time. Doing so, the desired results are returned for the 6_3_Hz and 20_Hz fields, but for the Overall_Spectra field SSMS returns:

select
         POWER(10.000000000000000000000000000000000000,Overall_Spectra/10)
         POWER(10.000000000000000000000000000000000000,"6_3_Hz"/10)
         Power(10.000000000000000000000000000000000000,"20_Hz"/10)
from sound;


Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting float to data type numeric.

I've referenced the following MS documentation in an attempt to understand what's going on (specifically the Return Types table,) but to no avail:

https://learn.microsoft.com/en-us/sql/t-sql/functions/power-transact-sql?view=sql-server-2017

I'm confused about the point at which SQL employs the float data type, as that seems to be in some way the cause of the error. Any insight into what SQL is doing, or what bone-headed thing I'm doing, would be greatly appreciated!!

B-Dawg
  • 31
  • 5
  • I would wager you have values in `Overall_Spectra` that are larger than 3 digits, by declaring it as `DECIMAL(3, 1)` it means this is valid: 99.9 or 90.9 but 100.0 is too big – Ryan Wilson Dec 07 '18 at 19:14
  • 1
    @dustinos3 I mistakenly rejected your edits by making an edit of my own (I omitted my T-SQL code). Your edit was great though, and I'm trying to make it look like you did... – B-Dawg Dec 07 '18 at 19:15
  • Go into edit mode and for code blocks highlight the text and use the icon `{}` – Ryan Wilson Dec 07 '18 at 19:16
  • @RyanWilson I don't think that's what's happening. I can reproduce the error with the posted data, of which Overall_Spectra conforms to decimal(3,1). – B-Dawg Dec 07 '18 at 19:18
  • @RyanWilson Thanks for the editing lesson! I'm a complete noob! Despite that, I still want to learn to do things correctly. Thanks!! – B-Dawg Dec 07 '18 at 19:19
  • I believe the overflow happens from your `POWER(10.00000000000, etc...` I was able to get the same overflow error by declaring a `decimal = 99.0` and passing that into POWER(10.00000...) instead of the value from `Overall_Spectra`. If you don't mind, why all the extra zeros after the decimal? Why not just `POWER(10.0, Overall_Spectra/10)`, when I changed it to 10.0 I didn't get the overflow anymore. – Ryan Wilson Dec 07 '18 at 19:21
  • @RyanWilson The format of the exponential base parameter seems to dictate the output format. Using 10 yields a result set of all zeros. Using 10.0 gives me all 0.0 values for my result set. To capture the really small numbers, I maxed out the decimals in the base. – B-Dawg Dec 07 '18 at 19:24
  • If I do this in SQL-Server: `DECLARE @decTest DECIMAL(3, 1) = 99.0 SELECT POWER(10.0,@decTest/10)` I get a value of `7943282347.2`. If I do it with all the extra zeros, I get the overflow error. – Ryan Wilson Dec 07 '18 at 19:26
  • @RyanWilson Also, these values will be fed into another calculation, so I wanted as much precision as possible. Thank you for taking the time to look at this! – B-Dawg Dec 07 '18 at 19:27
  • {decimal(8,10)} ought to do it for you. Not sure how low of a precision your trying to get. I learned this the hard way too. – junketsu Dec 07 '18 at 19:28
  • @RyanWilson Thank you! The examples in the MS documentation also included a lot of variables. I think the lesson for me is to add this step. I'm curious why this is, and if this principle applies to functions all together. This does seem to solve my problem. Looking for a way to mark this as the answer... – B-Dawg Dec 07 '18 at 19:35

1 Answers1

1

The maximum PRECISION on a DECIMAL at least in SQL Server 2014 is 38. You are using POWER(10), any number which the POWER produces a number with more than 38 digits will cause the overflow.

So for instance this: POWER(10.000000000000000000000000000000000000, 19.9 / 10) Will work as the number produced has <= 38 digits If you up the number to POWER(10.000000000000000000000000000000000000, 20.9 / 10) you get a number with more than 38 digits and then the overflow error

Please see this as reference to Precision and Scale:

(https://learn.microsoft.com/en-us/sql/t-sql/data-types/precision-scale-and-length-transact-sql?view=sql-server-2017)

Ryan Wilson
  • 10,223
  • 2
  • 21
  • 40