Oracle SQL Custom Sort with Union

Question

I have 2 tables that I'm unioning and want to custom order using ORDER BY CASE but I keep getting the ORA-01785: ORDER BY item must be the number of a SELECT-list expression error.

My 2 tables each have a column named "VISIT", containing (something similar to) the following data:

FollowUp table: BASELINE, 1_MONTH, 2_MONTH

Procedure table: PROCEDURE

I want to union these tables and sort in the following order: BASELINE, PROCEDURE, 1_MONTH, 2_MONTH

Here is what I think should work:

SELECT VISIT
FROM FollowUp
UNION
SELECT VISIT
FROM Procedure
ORDER BY
    CASE VISIT
    WHEN 'BASELINE' THEN 1
    WHEN 'PROCEDURE' THEN 2
    WHEN '1_MONTH' THEN 3
    WHEN '2_MONTH' THEN 4
    ELSE 5 END

However I'm getting the 01785 error. I've also tried replacing CASE VISIT with CASE 1 and get the same error. Thank you for the help!

Answer 1

Try doing this with a subquery:

select visit
from ((SELECT VISIT
       FROM FollowUp
      ) union
      (SELECT VISIT
       FROM Procedure
      )
     ) t
ORDER BY
    CASE VISIT
    WHEN 'BASELINE' THEN 1
    WHEN 'PROCEDURE' THEN 2
    WHEN '1_MONTH' THEN 3
    WHEN '2_MONTH' THEN 4
    ELSE 5 END;

If you do not need duplicate removal, then use union all.

Answer 2

Try this:

  SELECT x.visit,
         CASE x.VISIT
            WHEN 'BASELINE' THEN 1
            WHEN 'PROCEDURE' THEN 2
            WHEN '1_MONTH' THEN 3
            WHEN '2_MONTH' THEN 4
            ELSE 5
         END
            sort_by
    FROM (SELECT VISIT FROM FollowUp
          UNION ALL
          SELECT VISIT FROM Procedure) x
ORDER BY SORT_BY