Knowledge Base

BUG: Precision and Scale Change with SUM or AVG Functions

PSS ID Number: 167602

Article Last Modified on 10/15/2002

The information in this article applies to:

This article was previously published under Q167602
BUG #: 15699 (NT: 6.00)
       16647 (6.50)
       57616 (7.00)
       224386 (SHILOH)

SYMPTOMS

If a SUM or AVG function is applied to a numeric field, the field loses its precision and scale.

WORKAROUND

Use the CONVERT function to regain the precision and scale.

For example, in a case where a cursor declared to select AVG(<column>) and fetch fails with error 16923, declare the cursor like the following: 
   declare cur_test cursor for
   select convert(numeric(<precision>,<scale>),AVG(c1))
   from table1

STATUS

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

MORE INFORMATION

With a limited amount of testing, the SUM function seems to get a precision of 38 and retains the scale. The AVG function seems to get a precision of 38 and a scale of six if the original scale was less than six; otherwise, it retains its scale. This problem does not occur with MAX or MIN functions. You can observe this behavior with a table created with select into or with a view.

When you are trying to use a server side cursor to fetch the SUM or AVG of a numeric column, this problem causes error 16923:
Cursorfetch: Maximum length of '%s' is incompatible with that of
selected column in the cursor.

Keywords: kbBug kbDSupport kbusage KB167602
Technology: kbAudDeveloper kbSQLServ2000 kbSQLServ2000Search kbSQLServ600 kbSQLServ650 kbSQLServ700 kbSQLServSearch


Send feedback to Microsoft

© 2003 Microsoft Corporation. All rights reserved.