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
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.
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.