Microsoft KB Archive/237973

= BUG: Aggregate of an Expression May Return the Incorrect Scale =

Article ID: 237973

Article Last Modified on 11/17/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q237973



BUG #: 42324 (SQLBUG_70)

BUG #: 18771 (SQLBUG_65)



SYMPTOMS
When adding two expressions e1 and e2 with scales s1 and s2 (each a decimal type) the expected result for the scale is the max(s1,s2) for the result. For example: e1   = 10.0001     s1 = 4 e2   = 10.000001   s2 = 6  -- max

e1+e2 = 20.000101  scale = 6 When either the SUM or AVG aggregates are used in one of the expressions, the scale of the result may be incorrect. In the case of SUM, the resulting scale is the smallest scale provided as input to the SUM function. In the case of the AVG function, the scale is a fixed value that may not reflect either the scale of e1 or e2.



WORKAROUND
Here are two workarounds that you can implement to avoid this problem:
 * Use the CONVERT function to convert the output of the SUM or AVG function into a different scale.

-or-
 * Change the column definition in the base table to reflect the desired scale and the SUM function produces the desired results.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Keywords: kbbug kbpending KB237973

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.