Oracle – select custom order

October 25, 2022

SQL

select * from 
    (select status, count(*) from test1
    where trunc(update_ts) = trunc(sysdate) and status in  ('ACK','NACK')
    group by status)
    union
    (select 'SENT' as status, count(*) from test1
    where trunc(update_ts) = trunc(sysdate) 
    )
    
    order by decode(status, 'SENT', 1, 'ACK', 2, 'NACK', 3)

Error

ORA-01785: ORDER BY item must be the number of a SELECT-list expression

00000 – "ORDER BY item must be the number of a SELECT-list expression"

>Solution :

You are running into an order of operations issue. When working with unions, if you want to sort the entire unioned dataset, you need parenthesis around that dataset, which is treated as a subquery.

What you have effectively written in your query is

SELECT something
  FROM a union b
ORDER BY column

That just isn’t valid SQL and doesn’t evaluate well, resulting in your error message. If you wrap the union with parenthesis, the db engine sees the union as a subquery.

SELECT something
  FROM (SELECT column FROM a UNION SELECT column FROM b)
ORDER BY column

If you rewrite your query with the entire subquery in parenthesis, it will run fine.

    select * from 
    (select status, count(*) 
      from test1
     where trunc(update_ts) = trunc(sysdate) and status in  ('ACK','NACK')
     group by status
     union
     select 'SENT' as status, count(*) 
       from test1
      where trunc(update_ts) = trunc(sysdate) 
    )    
    order by decode(status, 'SENT', 1, 'ACK', 2, 'NACK', 3)

Note: I couldn’t find specific documentation in the Oracle docs about this. There are lots of online examples for various db platforms but nothing I would consider "official".

As a side note, it is a good habit to always alias your subquery. Oracle doesn’t care if it’s aliased or not but other RDBMS platforms do. If you build the habit now, transitioning to working with other database platforms will be easier.