Using Microsoft SQL Server 2008, I get the following error.
Msg 104, Level 16, State 1, Line 43
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
The query is I am using is kind of complex, but the
CASE statement in side of the
ORDER BY clause can not see the aliased column name, here is a brief example.
SELECT 1 AS foo, 2 AS bar UNION ALL SELECT 10 AS foo, 20 AS bar ORDER BY CASE WHEN foo = 2 THEN 1 END;
In my production query the left-query needs to be ordered by the column
[360_set] found in the table, and the right-query needs to be ordered as if
[360_set] was null.
How do I fix this error, and why does this syntax generate an error?
Here is the version info,
Microsoft SQL Server Management Studio 10.0.5512.0 Microsoft Analysis Services Client Tools 10.0.5500.0 Microsoft Data Access Components (MDAC) 6.1.7601.17514 Microsoft MSXML 3.0 6.0 Microsoft Internet Explorer 9.10.9200.16635 Microsoft .NET Framework 2.0.50727.5472 Operating System 6.1.7601