how to order sql query with union ( ORA-00904)

Question

I have huge query and i have to order by desc but i couldn't do that. I think there is something wrong with the union but i couldn't figure it out.

Query is working without using ORDER BY.

here is the query:

SELECT T.EMPLOYEE_NUMBER,
       F.REG_ID,
       F.DURATION_NETLINE,
       F.FLIGHT_NUMBER,
       F.DEPARTURE_DATE,
       USER_LIFUS.NAME,
       USER_LIFUS.SURNAME,
       F.FORM_RESULT_STATUS,
       T.LIEX_NUMBER,
       F.IS_PC_FLIGHT,
       F.IS_DC_FLIGHT,
       FT.NAME FLIGHT_TYPE,
       T.IS_RFLC,
       F.IS_DC_FLIGHT,
       F.LEG_ID
  FROM TRAINEE T
INNER JOIN FLIGHT F
    ON F.EVALUATED_EMP_NUM = T.EMPLOYEE_NUMBER
   AND F.DEPARTURE_DATE >= T.LIFUS_START_DATE
   AND (F.IS_SUBMITTED = 1 AND F.IS_DELETED = 0 AND F.IS_CANCELLED = 0)
   AND F.IS_DC_FLIGHT = 0
   AND F.IS_FC_FLIGHT = 0
   --and f.leg_id <>0
INNER JOIN USER_LIFUS
    ON USER_LIFUS.EMPLOYEE_NUMBER = F.INSTRUCTOR_EMP_NUM
INNER JOIN FLIGHT_TYPE FT
    ON F.FLIGHT_TYPE_ID = FT.ID
WHERE T.EMPLOYEE_NUMBER = '088416'
UNION ALL
SELECT T.EMPLOYEE_NUMBER,F.REG_ID,
       F.DURATION_NETLINE,
       F.FLIGHT_NUMBER,
       F.DEPARTURE_DATE,
       USER_LIFUS.NAME,
       USER_LIFUS.SURNAME,
       F.FORM_RESULT_STATUS,
       T.LIEX_NUMBER,
       F.IS_PC_FLIGHT,
       F.IS_DC_FLIGHT,
       FT.NAME FLIGHT_TYPE,
       T.IS_RFLC,
       F.IS_DC_FLIGHT,
       F.LEG_ID
  FROM TRAINEE T
INNER JOIN FLIGHT F
    ON F.EVALUATED_EMP_NUM = T.EMPLOYEE_NUMBER
   AND F.DEPARTURE_DATE >= T.LIFUS_START_DATE
   AND (F.IS_SUBMITTED = 1 AND F.IS_DELETED = 0 AND F.IS_CANCELLED = 0)
   AND F.IS_DC_FLIGHT = 1
   and f.is_fc_flight = 0
   AND F.LEG_ID <> 0
INNER JOIN USER_LIFUS
    ON USER_LIFUS.EMPLOYEE_NUMBER = F.INSTRUCTOR_EMP_NUM
INNER JOIN FLIGHT_TYPE FT
    ON F.FLIGHT_TYPE_ID = FT.ID
WHERE T.EMPLOYEE_NUMBER = '123456'

when i add

ORDER BY F.FLIGHT_NUMBER DESC

it gives me the

ora-00904

Answer 1

Assuming you only added the

ORDER BY F.FLIGHT_NUMBER DESC

At the bottom of the query, you need to add it outside of the two union-ed queries.

SELECT * FROM (
SELECT T.EMPLOYEE_NUMBER,
F.REG_ID,
       F.DURATION_NETLINE,
       F.FLIGHT_NUMBER,
       F.DEPARTURE_DATE,
       USER_LIFUS.NAME,
       USER_LIFUS.SURNAME,
       F.FORM_RESULT_STATUS,
       T.LIEX_NUMBER,
       F.IS_PC_FLIGHT,
       F.IS_DC_FLIGHT,
       FT.NAME FLIGHT_TYPE,
       T.IS_RFLC,
       F.IS_DC_FLIGHT,
       F.LEG_ID
  FROM TRAINEE T
INNER JOIN FLIGHT F
    ON F.EVALUATED_EMP_NUM = T.EMPLOYEE_NUMBER
   AND F.DEPARTURE_DATE >= T.LIFUS_START_DATE
   AND (F.IS_SUBMITTED = 1 AND F.IS_DELETED = 0 AND F.IS_CANCELLED = 0)
   AND F.IS_DC_FLIGHT = 0
   AND F.IS_FC_FLIGHT = 0
   --and f.leg_id <>0
INNER JOIN USER_LIFUS
    ON USER_LIFUS.EMPLOYEE_NUMBER = F.INSTRUCTOR_EMP_NUM
INNER JOIN FLIGHT_TYPE FT
    ON F.FLIGHT_TYPE_ID = FT.ID
WHERE T.EMPLOYEE_NUMBER = '088416'
UNION ALL
SELECT T.EMPLOYEE_NUMBER,F.REG_ID,
       F.DURATION_NETLINE,
       F.FLIGHT_NUMBER,
       F.DEPARTURE_DATE,
       USER_LIFUS.NAME,
       USER_LIFUS.SURNAME,
       F.FORM_RESULT_STATUS,
       T.LIEX_NUMBER,
       F.IS_PC_FLIGHT,
       F.IS_DC_FLIGHT,
       FT.NAME FLIGHT_TYPE,
       T.IS_RFLC,
       F.IS_DC_FLIGHT,
       F.LEG_ID
  FROM TRAINEE T
INNER JOIN FLIGHT F
    ON F.EVALUATED_EMP_NUM = T.EMPLOYEE_NUMBER
   AND F.DEPARTURE_DATE >= T.LIFUS_START_DATE
   AND (F.IS_SUBMITTED = 1 AND F.IS_DELETED = 0 AND F.IS_CANCELLED = 0)
   AND F.IS_DC_FLIGHT = 1
   and f.is_fc_flight = 0
   AND F.LEG_ID <> 0
INNER JOIN USER_LIFUS
    ON USER_LIFUS.EMPLOYEE_NUMBER = F.INSTRUCTOR_EMP_NUM
INNER JOIN FLIGHT_TYPE FT
    ON F.FLIGHT_TYPE_ID = FT.ID
WHERE T.EMPLOYEE_NUMBER = '123456')
ORDER BY FLIGHT_NUMBER DESC

Answer 2

Do like this :

select temp.T.Employee_number,temp.F.REG_ID,temp.F.FLIGHT_NUMBER....
from (  --your query mentioned above-- ) temp
order by temp.F.FLIGHT_NUMBER desc;