File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes missing right parenthesis Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "missing right parenthesis" Watch "missing right parenthesis" New topic

missing right parenthesis

kittu shusma

Joined: Apr 18, 2012
Posts: 14

select sum(ISSUED_COPIES - returned_copies) FROM lm_issued_book_dtls issueBook
where ACCESSION_DTLS_ID in (select ACCESSION_DTLS_ID from lm_accession_dtls
where book_id in (select book_id from lm_book_dtls order by lm_book_dtls.book_code ));

ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
Error at Line: 3 Column: 51

Whats wrong? Experts please help.. I am new to Oracle 11g
Matthew Brown

Joined: Apr 06, 2010
Posts: 4543

I'm not sure it's the problem, but there's no need for the ORDER BY clause in a subquery (the book ID is in the list or not regardless of the order). I'd get rid of that and see if the error is still there or different.
Wendy Gibbons

Joined: Oct 21, 2008
Posts: 1107

just re formatted it in a format i am used to.
Agree with matthew need to remove the internal order by. That might actually be the problem, or could just be a performance issue.

And why is this 2 sub selects not just 1 select with joins, or at least an exists clause?

Wendy Gibbons

Joined: Oct 21, 2008
Posts: 1107

From memory missing right parentesis errors are just a vauge, failed to parse error, and very often have nothing to do with the brackets.

Where did you get this statement from, have you typed into javaranch what you think the code is running? or have you copy and pasted it out of some debugging statement?
Wei Dai
Ranch Hand

Joined: Jun 22, 2005
Posts: 86
order by lm_book_dtls.book_code should be removed otherwise some sql engine will ignore it or throw exceptions.
chris webster

Joined: Mar 01, 2009
Posts: 2289

Everybody's right:

  • The error message itself says the error is at line 3, column 51. Assuming the SQL you gave us was presented to your SQL interpreter in the same format, then Oracle found the problem at the "ORDER BY" in the nested query. So Oracle's right - you just need to read the message
  • You can't put "ORDER BY" in a nested sub-query like this in Oracle, and logically it is pointless anyway. So Matthew, Wendy and Wei Dai are right too.
  • "Missing right parenthesis" errors often occur in Oracle where your SQL syntax has gone wrong and the SQL parser hits an unexpected element or runs out of SQL before it's figured out what you're trying to do. So Wendy's right here as well.

  • Take out the unnecessary ORDER BY, use a single sub-query with a join, as Wendy advises, and try again.

    No more Blub for me, thank you, Vicar.
    I agree. Here's the link:
    subject: missing right parenthesis
    It's not a secret anymore!