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"
CodePudding user response:
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.