ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
Solution 1:
To fix this problem I put my Union statement into a derived table by doing the following.
SELECT * FROM (
SELECT Column1, Column2 FROM Table_A
UNION ALL
SELECT Column1, Column2 FROM Table_B
) DerivedTableName (Column1, Column2)
Next I added in my dynamic Order By code as such.
SELECT * FROM (
SELECT Column1, Column2 FROM Table_A
UNION ALL
SELECT Column1, Column2 FROM Table_B
) DerivedTableName (Column1, Column2)
ORDER BY
CASE @OrderBy WHEN ‘Column1ASC’ THEN Column1 END ASC,
CASE @OrderBy WHEN ‘Column1DESC’ THEN Column1 END DESC
Solution2:
SELECT Z.ax, Z.by, Z.cz
FROM (SELECT a,b,c FROM X
UNION
SELECT x,y,z FROM Y)AS Z Order by a
