| ORA-00907: missing right parenthesis [message #414719] |
Thu, 23 July 2009 05:26  |
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 #414724 is a reply to message #414719] |
Thu, 23 July 2009 05:36  |
ThomasG
Messages: 3211
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 #414726 is a reply to message #414725] |
Thu, 23 July 2009 05:39 |
ThomasG
Messages: 3211
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 #414744 is a reply to message #414725] |
Thu, 23 July 2009 06:33 |
pablolee
Messages: 2882
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 #414788 is a reply to message #414785] |
Thu, 23 July 2009 08:15 |
ThomasG
Messages: 3211
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. 
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
