Home » SQL & PL/SQL » SQL & PL/SQL » Subselect union (Oracle 11g)
Subselect union [message #350019] Tue, 23 September 2008 14:50 Go to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Can somebody explain to me why the following query gives me a 'Missing right parenthesis' error?

select rec_type from (
select rec_type from some_table where rec_type < 30 and rownum=1 order by rec_type desc union
select rec_type from some_table where rec_type > 35 and rownum=1 order by rec_type);
Re: Subselect union [message #350020 is a reply to message #350019] Tue, 23 September 2008 14:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Order by is only after all unions. You can't (and it is meaningless to) order each side of a union.

In addition "order by" on a result with only 1 row is simply s...

Also for your next post, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
And always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Tue, 23 September 2008 14:56]

Report message to a moderator

Re: Subselect union [message #350021 is a reply to message #350020] Tue, 23 September 2008 15:00 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
You're right. My query was silly. What I was actually trying to do is this:

select rec_type from (select * from
(select rec_type from legacy_index.mrte_index where rec_type < 30 order by rec_type desc) where rownum=1 union
select * from
(select rec_type from legacy_index.mrte_index where rec_type > 35 order by rec_type) where rownum=1);

Which works as I need. Thanks for your response.
Re: Subselect union [message #350081 is a reply to message #350021] Wed, 24 September 2008 01:20 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you like our help then pleas help us help you and follow the guidelines, especially formatting your queries.

Regards
Michel
Previous Topic: Updating Distinct Values
Next Topic: using to_date command to display specific month
Goto Forum:
  


Current Time: Thu Dec 08 12:38:25 CST 2016

Total time taken to generate the page: 0.23688 seconds