Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

missing right parenthesis

 
kittu shusma
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
helllo....


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"
*Cause:
*Action:
Error at Line: 3 Column: 51

Whats wrong? Experts please help.. I am new to Oracle 11g
 
Matthew Brown
Bartender
Posts: 4568
9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 1111
Eclipse IDE Oracle VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
order by lm_book_dtls.book_code should be removed otherwise some sql engine will ignore it or throw exceptions.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic