Two Solutions to fix issue: ORDER BY items must appear in the select list if the statement contains a UNION, ...

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