BUG: SUM in Subquery has NULL precision and scale (173386)
The information in this article applies to:
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.
Modification Type: | Major | Last Reviewed: | 10/3/2003 |
---|
Keywords: | kbBug kbusage KB173386 |
---|
|