Curious issue with Oracle UNION and ORDER BY

by

This doesn’t really answer the question, but it seems to be a parser bug (or ‘feature’) rather than a language requirement.

According to My Oracle Support, this seems to have been raised as bug 14196463 but closed with no resolution. It’s also mentioned in community thread 3561546. You need a MOS account, or at least an Oracle account, to see either of those though.

It’s also been discussed in an OTN thread which requires a basic Oracle login rather than a MOS account, as far as I can tell. That also doesn’t have much information but repeats your findings, and also suggests the behaviour has existed back at least to 9.2.0.8 and perhaps much earlier.

The documentation is a bit vague but doesn’t indicate this is expected to be a problem:

For compound queries containing set operators UNION, INTERSECT, MINUS, or UNION ALL, the ORDER BY clause must specify positions or aliases rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query.

You are aliasing your expression and using that, and it doesn’t say you have to alias particular components (although of course it doesn’t say you don’t have to either).

The behaviour seems inconsistent with the alias being valid for the final projection, and the usual rule about the alias only being valid in the order by clause – this seems to be falling down somewhere in between.

More Related Contents:

  1. I want to search job and salary from employee table but I am getting error like invalid relational operator [closed]
  2. How do I do top 1 in Oracle? [duplicate]
  3. How to use Oracle ORDER BY and ROWNUM correctly?
  4. REGEX to select nth value from a list, allowing for nulls
  5. Oracle Joins – Comparison between conventional syntax VS ANSI Syntax
  6. SQL (ORACLE): ORDER BY and LIMIT [duplicate]
  7. How can I select from list of values in Oracle
  8. PLS-00428: an INTO clause is expected in this SELECT statement
  9. How to get the last row of an Oracle table
  10. Using pivot on multiple columns of an Oracle row
  11. Pivoting of data using two columns
  12. INSERT SELECT statement in Oracle 11G
  13. Sleep function in ORACLE
  14. SQL Error: ORA-00942 table or view does not exist
  15. Oracle – How to create a materialized view with FAST REFRESH and JOINS
  16. Nth max salary in Oracle
  17. Oracle – ORA-01489: result of string concatenation is too long [duplicate]
  18. Display names of all constraints for a table in Oracle SQL
  19. Custom Order in Oracle SQL
  20. Oracle 11g – Check constraint with RegEx
  21. Issue when comparing result of to_char(myDate, ‘DAY’) to a string
  22. How to use a dynamic parameter in a IN clause of a JPA named query?
  23. Why does the wm_concat not work here?
  24. The network adapter could not establish the connection – Oracle 11g
  25. Oracle string aggregation
  26. Subtracting Dates in Oracle – Number or Interval Datatype?
  27. How to call Oracle MD5 hash function?
  28. Oracle RAC and sequences
  29. Using bind variables with dynamic SELECT INTO clause in PL/SQL
  30. Insert into from CTE

Leave a Comment