Microsoft KB Archive/173386

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 15:37, 18 July 2020 by 3155ffGd (talk | contribs) (importing KB archive)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base


BUG: SUM in Subquery has NULL precision and scale

Article ID: 173386

Article Last Modified on 10/3/2003



APPLIES TO

  • Microsoft SQL Server 6.5 Standard Edition



This article was previously published under Q173386

SYMPTOMS

BUG #: 1 7224

A SELECT that contains a subquery that performs a SUM will have scale and precision set to NULL for the summed field. For example, the following statement will show this behavior:

   SELECT f1, (SELECT SUM(f2) SUMCOL FROM table2) FROM table1
                

A view created from this type of SELECT statement will display a NULL as the precision and scale. Also, a table created from a SELECT INTO statement will also have the precision and scale set to NULL.

The net effect of this is that applications like Microsoft Access will use the summed field as Text(255) because of the NULL scale and precision. As a result, Access will not allow numeric processing on the field.

WORKAROUND

To work around this problem, create a view that contains the subquery with the SUM, and use a join to bring the two queries together. The following script shows an example of the workaround:

   create table abc1
   (
   mykey integer
   )
   go

   create table abc2
   (
   mykey integer,
   mybucks money
   )
   go

   create view abcview2
   as
   select mykey, mysum(mybucks) mycol from abc group by mykey
   go

   create view abcview
   as
   select abc1.mykey, abcview2.mycol from abc1 join abcview2 on
   abc1.mykey = abcview2.mykey
   go
                

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Keywords: kbbug kbusage KB173386