Microsoft KB Archive/171869

= BUG: CREATE VIEW Fails with Message 8120 =

Article ID: 171869

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q171869



SYMPTOMS
BUG #: 16728 (NT: 6.5)

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: kbbug kbusage KB171869

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.