Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Using order by in a union (Read 7684 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Using order by in a union
20.09.06 at 11:11:22
 
Using order by in a union
 
In Oracle you are not allowed to use an order by clause in a select statement in which you use a union operator.
Example:
select object_name from all_objects where owner = 'SYS' order by object_name
union
select object_name from all_objects where owner = 'SYSTEM' order by object_name

 
Oracle displays the error: ORA-00904: invalid column name.
 
In certain situations you would like to use the order by. In the next example I am trying to output a report in which I am adding a text line above the data:
select text
from
(
select 1 seq, 'All SYS objects' text from dual
union
select 2 seq, object_name text from all_objects
where owner = 'SYS'
order by object_id
)
order by seq

 
The order by seq is necessary to display my line All SYS objects at the top. I would then like to see all objects to be listed in order. I am getting the above mentioned error.
 
I found that when you wrap the select statement with the order by clause in an inline view that Oracle is happy to run it:
select text
from
(
select 1 seq, 'All SYS objects' text from dual
union
select * from
(select 2 seq, object_name text from all_objects
where owner = 'SYS'
order by object_id
)
)
order by seq
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1