BUG: SELECT INTO from View with Aggregate Function

• Microsoft SQL Server version 4.2 for OS/2

SYMPTOMS

When you run a SELECT INTO query that references a view that has a column defined with an aggregate function, the following error will occur:

   Can not run query -- the referenced object (name NAME
   NOT RECOVERABLE) dropped during query optimization.
   (msg 225, level 16, state 1)

   create view myview as
   select stor_id, sum_qty=sum(qty)
   from sales
      group by stor_id
      go
      select stor_id,sum_qty
      into mytab
      from myview
      go

CAUSE

SQL Server incorrectly handles queries that reference a view that has a column defined with an aggregate function. If the above query is reconstructed such that the view is bypassed, the query runs without problems. For instance, the following query

     select stor_id, sum_qty=sum(qty)
     into mytab
     from sales
     group by stor_id

WORKAROUND

Reconstruct the query so that views are bypassed, or create a permanent table in place of a view if necessary.

STATUS

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