Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author 
Topic 
friction
Starting Member
5 Posts 
Posted  20071113 : 23:20:46

Hi Guys, here's one that's doing my head in:
SITUATION
I have a table with field names such as [4995], [7090] and [7080], each with the datatype decimal(38,19).
PROBLEM
I have a view in the server management studio that contains: [4995] / ([7090]  [7080]) AS Result. However the result is only 6 decimal places long, when it should be 19 decimal places long.
THE PLOT THICKENS and EXAMPLE CODE
SELECT CompanyCode, PeriodEndDate, [4995], [7090], [7080], [4995] / ([7090]  [7080]) AS Result, 6716000.000 / (183239000.000  141542000.000) AS Result2, 6716000.000000000000000000 / (183239000.000000000000000000  141542000.000000000000000000) AS Result3 FROM dbo.RawData WHERE (CompanyCode = 'AAC') AND (PeriodEndDate = CONVERT(DATETIME, '20051230 00:00:00', 102))
with a result of:
AAC 30/12/2005 12:00:00 AM 6716000.0000000000000000000 183239000.0000000000000000000 141542000.0000000000000000000 0.161066 0.16106674341079693 0.1610667434107
Notice that the exact same calculation using .000 at the end of each integer gives me a result with MORE precision than using 18 zero's after the decimal point?
I'VE ALSO TRIED
I've also tried using CAST() around each reference to a field, and around the end result, but all it does is pad the 6 decimal places out with zero's on the end of it.
WHY AM I USING THESE DATATYPES?
I'm forced to use the DECIMAL(38,19) datatype because some of the values are 19 digits long with no decimal, and others are zero with 19 digits of fractional component, and I can't have the loss of accuracy that comes with using floats.
Does anyone know what's going on? 

PeterNeo
Constraint Violating Yak Guru
357 Posts 
Posted  20071113 : 23:51:27

Try this
CONVERT(DECIMAL(38, 19), [4995] / ([7090]  [7080])) AS Result 


dataguru1971
Master Smack Fu Yak Hacker
1464 Posts 
Posted  20071114 : 00:01:16

quote: Originally posted by PeterNeo
Try this
CONVERT(DECIMAL(38, 19), [4995] / ([7090]  [7080])) AS Result
That won't do it...should have tested :) Check result of R2 below..
Create Table #Dec (num1 numeric(38,19) not null, num2 numeric(38,19) not null, num3 numeric(38,19) not null)
Insert Into #Dec (num1,num2,num3) Select 6716000,183239000,141542000
Select num1 / (num2num3) as NOCHANGE, convert(float,num1) / (convert(float,num2)  convert(float,num3)) as R1, convert(numeric(38,19), num1/(num2num3)) as R2, (num1 + .0000000000000000000)/((num2 + .00000000000000)(num3 + .0000000000000000)) as R3 FROM #DEC
DROP Table #DEC
Results
Nochange .161066 R1 0.16106674341079694 R2 .1610660000000000000 R3 .161066
Not sure what the answer is, but float seems to work best. I tried a number of combinations which only resulted in protracted results at the sixth place.
Poor planning on your part does not constitute an emergency on my part.



friction
Starting Member
5 Posts 
Posted  20071114 : 05:44:33

I'd love to use float, but I can't have values changing even if it's seemingly small like after the 6th decimal place. I just don't understand why it truncates (it doesn't round, it truncates) after 6 decimal places. Why 6? Why does that change if I use less trailing zero's? Surely there's a help file entry that explains this behaviour. 


SwePeso
Patron Saint of Lost Yaks
30421 Posts 
Posted  20071114 : 06:26:58

Beware when using FLOAT. They are not exact! A value stored as FLOAT is translated into nearest binary equivalent.
E 12°55'05.25" N 56°04'39.16" 


dataguru1971
Master Smack Fu Yak Hacker
1464 Posts 
Posted  20071114 : 07:24:57

quote: Originally posted by Peso
Beware when using FLOAT.
So is the resulting math incorrect, or only the value eventually stored? what if the resulting float calcation were stored as a varchar(38) field?
quote:
I'd love to use float, but I can't have values changing even if it's seemingly small like after the 6th decimal place. I just don't understand why it truncates (it doesn't round, it truncates) after 6 decimal places. Why 6? Why does that change if I use less trailing zero's? Surely there's a help file entry that explains this behaviour.
This is happened to me in some instances even with only 4 decimals needed. Would love to find out why.
Poor planning on your part does not constitute an emergency on my part.



friction
Starting Member
5 Posts 
Posted  20071114 : 08:28:01

If I store the initial values as float, they don't store exactly as they've been provided, which means that the resulting value after the division is incorrect. Alternatively if I continue to use decimals for the initial values, the result is truncated after 6 decimal places, even if I cast/convert it back into a decimal(38,19) (it just adds trailing zeros). I tested it to make sure, but casting it into a varchar(255) didn't do anything, the result is still 0.161066, only now it's in a string. 


friction
Starting Member
5 Posts 
Posted  20071114 : 22:06:55

Here's an update to this situation, which I still haven't figured out a good solution to:
Reducing the precision of the decimal data type increases the resulting precision after the division. This can be proved as such:
SELECT CompanyCode, PeriodEndDate, CAST(6716000 AS decimal(38, 19)) / (CAST(183239000 AS decimal(38, 19))  CAST(141542000 AS decimal(38, 19))) AS Result1, CAST(6716000 AS decimal(31, 19)) / (CAST(183239000 AS decimal(31, 19))  CAST(141542000 AS decimal(31, 19))) AS Result2, CAST(6716000 AS decimal(28, 19)) / (CAST(183239000 AS decimal(28, 19))  CAST(141542000 AS decimal(28, 19))) AS Result3, CAST(6716000 AS decimal(24, 15)) / (CAST(183239000 AS decimal(24, 15))  CAST(141542000 AS decimal(24, 15))) AS Result4, CAST(6716000 AS decimal(13, 4)) / (CAST(183239000 AS decimal(13, 4))  CAST(141542000 AS decimal(13, 4))) AS Result5 FROM dbo.RawData WHERE (CompanyCode = 'AAC') AND (PeriodEndDate = CONVERT(DATETIME, '20051230 00:00:00', 102))
Returns the following:
Result1: 0.161066 Result2: 0.1610667 Result3: 0.1610667434 Result4: 0.16106674341079 Result5: 0.1610667434107969398
Why that is, I don't know. How to get around it when my largest value is 12 digits to the left of the decimal point, and my value with the most decimal places is 19 digits to the right of the decimal point, where I can't afford to lose data through the float data type, I don't know. 


friction
Starting Member
5 Posts 
Posted  20071114 : 22:22:02

Well, it's not a solution, but an explanation:
http://msdn2.microsoft.com/enus/library/ms190476.aspx
Operator: e1  e2 Result precision: max(s1, s2) + max(p1s1, p2s2) + 1 Result scale: max(s1, s2)
Operator: e1 / e2 Result precision: p1  s1 + s2 + max(6, s1 + p2 + 1) Result scale *: max(6, s1 + p2 + 1) * The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.
I tried to do the maths to figure out why it's getting a scale of 6 when decimal(38,19) is used, but I failed miserably, but I guess this explains it. 


dataguru1971
Master Smack Fu Yak Hacker
1464 Posts 
Posted  20071114 : 22:37:16

Since the resulting precision of such math would result in the necessity of higher precision based on BOL, it is almost understandable.
an operation of this: e1 / e2 result precision: p1  s1 + s2 + max(6, s1 + p2 + 1)
and a result of: max(6, s1 + p2 + 1)
So if precision of e1 / e2 are 38 and 38 and scale of 19,19
38  19 + 19 + max(6, 19 + 38 + 1)
so 38 + 58 = 96... a number that is too large to fit 38,19 so it gets truncated to 38,6
By definition, SQL will automatically only preserve a precision/scale of 6 as a means to prevent the "integral part of the calculation from being truncated".
So applying the BOL logic to the 13,4 scenario, it actually makes sense
p1  s1 + s2 + max(6, s1 + p2 + 1) 13  4 + 4 + max(6,4 + 13+1) = 31
So your last result
had a precision of 12, but a scale of 19 decimal places fits as a result of the division within the max potential scale/precision and can be used, therefore smaller precision/scale can in fact return more decimal places as a result.
It is interesting, but since the maximum number to left and right of decimal places is not implicitly carried to the result (the formulas above are from BOL)..it sort of kind makes sense.
Poor planning on your part does not constitute an emergency on my part.



dataguru1971
Master Smack Fu Yak Hacker
1464 Posts 
Posted  20071114 : 22:38:43

Jeeze, we both were posting the same thing. Now I look like a troll, or worse a Rahul. At least we both know how to read BOL !
Cheers. I am satisfied with the explanation, and it makes sense to me at least.
Poor planning on your part does not constitute an emergency on my part.







