BUG: CREATE VIEW Fails with Message 8120

Last reviewed: July 30, 1997
Article ID: Q171869
The information in this article applies to:
  • Microsoft SQL Server, version 6.5
BUG #: 16728 (NT: 6.5)

SYMPTOMS

A CREATE VIEW statement may fail to create the view, and cause message 8120:

   Column is invalid in the select list because it is not contained in
   either an aggregate function or the GROUP BY clause.

This problem occurs if all of the following conditions are true:
  • The query involves a correlated subquery.
  • The select list includes the column grouped by.
  • An operation is performed on the column in the select list.
  • The same operation is performed on the same column in the GROUP BY.

The following script demonstrates this problem:

   create view viewname
   as
   select 'x'=substring(x,1,1)
   from
   tbl1 a
   where a.y=(select max(y) from tbl1
                            where y = a.y)
   group by substring(x,1,1)

WORKAROUND

To work around this problem, do either of the following:

  • Rewrite the query, removing the operation from the GROUP BY clause.

    -or-

  • Rewrite the query without the correlated subquery.

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.

MORE INFORMATION

The SELECT statement works fine outside of the view. The GROUP BY clause, as documented, should accept any aggregate free expression. This problem does not occur on SQL Server build 6.5.201.

Keywords          : kbbug6.50 SSrvBCP SSrvGen kbusage
Version           : 6.5
Platform          : WINDOWS
Issue type        : kbbug
Solution Type     : kbworkaround


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: July 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.