Oracle: Order by Union returning ORA-00933: SQL command not properly ended

ora-00933, oracle, sql-order-by, union

Solution

try this code:

select  e1.name name /* e1.* */
  from employee e1
 where employee_id = 2
union
select 
  e2.name name /* e2.* */
  from employee e2
 where employee_id = 3
 order by name;

if you want to order the result of first query then to order the result the second query so you can do like this:

select 1 query, e1.name name /* e1.* */
  from employee e1
 where employee_id = 2
union
select 
 2 query, e2.name name /* e2.* */
  from employee e2
 where employee_id = 3
 order by query, name;

Problem

I have an issue with using Oracle's union and order by clauses together. I have two complex queries (with sub queries in them) having an order by clause for each of them. I need to union the output of both and return the result. When I run it, I am getting the error ORA-00933: SQL command not properly ended. But it works when I comment out the order by clauses in both of them. To test this, I created a simple query as simple as shown below ``` select * from employee where employee_id=2 order by name union select * from employee where employee_id=3 order by name; ``` Even this gave the same error when ran with order by clauses but runs well when I commentout the order by clauses. I tried searching forums, but I could not get solution for the exact problem. I found one at ORACLE Query with ORDER BY and UNION but As my queries are already too complecated because of subqueries and joins between too many tables, I dont want to implement this. Can someone help me on fixing the root cause of the issue.

Original source