Article ID: 2873474 - View products that this article applies to.
Consider the following scenario:
For example, you run the following query:
Msg 8120, Level 16, State 1, Line 3
Column '<column name>' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
In the query, the statement "when 1=2 then t1.col1 else 10" evaluates to 10. When you replace "10" by using NULL, this issue occurs.
create database test go use test go create table tvt ( v1t binary(1) null) create table tln (ln numeric(5) not null) go select case when 1=2 then t1.col1 else 10 end, case when t1.col1 = 2 then t1.col2 end from ( select t2.v1t as col1, 10 as col2 from tvt t2 ) t1 group by case when 1=2 then t1.col1 else 10 end, case when t1.col1 = 2 then t1.col2 end go
Note This issue does not occur when you remove either of the CASE statements from the SELECT statement or from the GROUP BY statement.
After you apply the fix, you have to enable a trace flag to make the hotfix work. Please contact Microsoft support when you need to apply this fix. The issue was first fixed in the following cumulative update of SQL Server.
Cumulative Update 6 for SQL Server 2012 SP1
|kbqfe kbfix kbsurveynew kbexpertiseadvanced KB2873474|