BUG: Precision and Scale Change with SUM or AVG Functions

• Microsoft SQL Server, versions 6.0 and 6.5

       16647 (6.50)

SYMPTOMS

If a SUM or AVG function is applied to a numeric field, the field looses 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 this to be a problem in Microsoft SQL Server versions 6.0 and 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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: