Symptoms

Consider the following scenario:

  • You have a computer that has Microsoft SQL Server 2012 installed.

  • You have a query that contains a CASE statement in a SELECT statement and a CASE statement in a GROUP BY statement.

  • The two CASE statements evaluate to NULL.

  • You run the query.

In this scenario, you receive the following error message:

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.

For example, you run the following query: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 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.
Note This issue does not occur when you remove either of the CASE statements from the SELECT statement or from the GROUP BY statement.

Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:


Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Any additional feedback? (Optional)

Thank you for your feedback!

×