Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00907: missing right parenthesis (10.2.0.3.0)
ORA-00907: missing right parenthesis [message #414719] Thu, 23 July 2009 05:26 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I am gettting following error while executing the following sql query

Query :

(SELECT * FROM DUAL ORDER BY 1);

Error :

"ORA-00907: missing right parenthesis" exception?

The same query works fine if it is not put within brackets. I am facing this problem since I have to return some value in a case statement as below

SELECT
CASE
WHEN EXISTS (......) THEN 'AB1'
ELSE (SELECT COL4 FROM TAB1 WHERE ROWNUM = 1 ORDER BY 1)
END
FROM DUAL;

Please let me know the reason?

Thank you,
SNN
Re: ORA-00907: missing right parenthesis [message #414723 is a reply to message #414719] Thu, 23 July 2009 05:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Laughing One more good fun.
WHERE ROWNUM = 1 ORDER BY 1

How many way you can order 1 row?

Regards
Michel
Re: ORA-00907: missing right parenthesis [message #414724 is a reply to message #414719] Thu, 23 July 2009 05:36 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Oracle doesn't allow the " ORDER BY 1" in that place, because only one row is allowed to be returned.

SQL> SELECT
  2  CASE
  3  WHEN 1 = 1 THEN 'AB1'
  4  ELSE (SELECT 'XX3' FROM dual WHERE ROWNUM = 1 ORDER BY 1)
  5  END
  6  FROM DUAL;
ELSE (SELECT 'XX3' FROM dual WHERE ROWNUM = 1 ORDER BY 1)
                                              *
ERROR at line 4:
ORA-00907: missing right parenthesis


SQL>
SQL> SELECT
  2  CASE
  3  WHEN 1 = 1 THEN 'AB1'
  4  ELSE (SELECT 'XX3' FROM dual WHERE ROWNUM = 1)
  5  END
  6  FROM DUAL;

CAS
---
AB1

SQL>


(Over 500 Posts, and STILL not able to post a formatted copy and paste?)
Re: ORA-00907: missing right parenthesis [message #414725 is a reply to message #414723] Thu, 23 July 2009 05:37 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michael ,

want to know the reason why it's giving error?
Please let me know the cause?

Thank you,
SNN
Re: ORA-00907: missing right parenthesis [message #414726 is a reply to message #414725] Thu, 23 July 2009 05:39 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
user71408 wrote on Thu, 23 July 2009 12:37

Please let me know the cause?


Quote:

Oracle doesn't allow the " ORDER BY 1" in that place, because only one row is allowed to be returned.



Those who can read have it SO much simpler in life.
Re: ORA-00907: missing right parenthesis [message #414728 is a reply to message #414725] Thu, 23 July 2009 05:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
user71408 wrote on Thu, 23 July 2009 12:37
Michael ,

want to know the reason why it's giving error?
Please let me know the cause?

Thank you,
SNN

It is invalid. You know the link, I don't have to post it.

Regards
Michel

[Updated on: Thu, 23 July 2009 05:41]

Report message to a moderator

Re: ORA-00907: missing right parenthesis [message #414729 is a reply to message #414724] Thu, 23 July 2009 05:43 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Thomas,

In SUbquery we are retrieving daat from TAB1 not from Dual.
Please look into this and let me know the cause.

Thank you,
SNN
Re: ORA-00907: missing right parenthesis [message #414730 is a reply to message #414729] Thu, 23 July 2009 05:44 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

In SUbquery we are retrieving daat from TAB1 not from Dual.



Yes. But as usual you are to lazy to post a test case. And I told you the cause.

[Updated on: Thu, 23 July 2009 05:45]

Report message to a moderator

Re: ORA-00907: missing right parenthesis [message #414744 is a reply to message #414725] Thu, 23 July 2009 06:33 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
user71408 wrote on Thu, 23 July 2009 11:37

Please let me know the cause?


[RANT]
You are the cause, you and your complete and utter innability to understand even the mosty basic principles of Oracle, SQL, forum ettiquette, basic reading and comprehension. You would be out of your depth in a car park puddle. It constantly amazes me that people continue to indulge you. [/RANT]
Re: ORA-00907: missing right parenthesis [message #414785 is a reply to message #414729] Thu, 23 July 2009 08:07 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
user71408 wrote on Thu, 23 July 2009 06:43

Please look into this and let me know the cause.



Yes Thomas, you better look into this for this moron. And get to it right now!
Re: ORA-00907: missing right parenthesis [message #414788 is a reply to message #414785] Thu, 23 July 2009 08:15 Go to previous message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes, Bwana. I have looked into it again Bwana. Here are some test cases, Bwana :

SQL>
SQL>
SQL>
SQL> SELECT 1 FROM dual;

         1
----------
         1

SQL>
SQL>
SQL> SELECT 1 FROM dual ORDER BY 1;

         1
----------
         1

SQL>
SQL>
SQL> (SELECT 1 FROM dual ORDER BY 1);
(SELECT 1 FROM dual ORDER BY 1)
                    *
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL>
SQL>
SQL> (SELECT 1 FROM dual WHERE ROWNUM = 1 ORDER BY 1);
(SELECT 1 FROM dual WHERE ROWNUM = 1 ORDER BY 1)
                                     *
ERROR at line 1:
ORA-00907: missing right parenthesis


SQL>
SQL>
SQL> SELECT * FROM (SELECT 1 FROM dual ORDER BY 1) WHERE ROWNUM = 1;

         1
----------
         1

SQL>
SQL>
SQL> (SELECT * FROM (SELECT 1 FROM dual ORDER BY 1) WHERE ROWNUM = 1);

         1
----------
         1

SQL>


They should now make the case abundantly clear to basically anyone who has some basic understanding on how computers and databases and Oracle works.

I now eagerly await the next "I don't get it. I get error. Look into it." reply from our addleheaded friend. Laughing
Previous Topic: error in package
Next Topic: Copy os file using pl/sql with different users credential (merged 6)
Goto Forum:
  


Current Time: Wed Dec 07 12:35:37 CST 2016

Total time taken to generate the page: 0.13717 seconds