DA-SOFT Technologies

Additional

UNION & ORDER BY & ORA-00904

Posted by Support [Sep 13, 2009]

The simple query with UNION and ORDER BY may fail on Oracle, while works on other DBMS's.

Today while testing AnyDAC {LIMIT(skip, rows)} macro function, I have found that following query fails on Oracle:

select * from dual
union all
select * from dual
order by dummy

with error ORA-00904: "DUMMY" invalid identifier. While similar by the structure query is working on MS SQL Server, IBM DB2, Postgre SQL and so on. Simple investigation showed, that following queries are working well on Oracle:

select dummy from dual
union all
select dummy from dual
order by dummy

select * from dual
union all
select dummy from dual
order by dummy

select * from dual
union all
select * from dual
order by 1

So, the conclusion - if you use the '*' as a SELECT list for all the queries in an UNION, then you cannot reference to the columns by their names in an ORDER BY clause.

Well ... The Oracle can fail too :)

Note 1, 14-Sep-2009

I have found docu part, explaining the situation. So, that is documented behaviour, although I does not see the reason for that.


	Add Comments