Oracle select query with inner select query error

Question

I am getting a SQL error and trying to resolve, any pointers would be helpful,

// this gets executed

SELECT empid FROM employees WHERE deptid IN (10,20,30,40 );

// this gets executed

SELECT deptid FROM department WHERE description LIKE '%application%' 
  ORDER BY createddate DESC

but the below query throws error:

SELECT empid  FROM employees WHERE deptid IN (SELECT deptid FROM department WHERE description LIKE '%application%' 
  ORDER BY createddate DESC);

error: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"

Update: 07/14:

Updating with the exact solution from @dasblinkenlight:

The problem is placing ORDER BY in the WHERE clause subquery. SQL syntax does not allow you to order elements of the subquery in the WHERE clause, because it does not change the result of the query overall

This article well explains many of the concepts - http://oraclequirks.blogspot.com/2008/01/ora-00907-missing-right-parenthesis.html

"ORA-00907: missing right parenthesis Clearly when one gets a message like this, the first reaction is probably to verify what parenthesis has been left out, but unfortunately there are no missing parentheses at all in this statement.

To cut it short, the untold syntax quirk is summarized as follows: don't use ORDER BY inside an IN subquery.

Now, one may object that indeed it doesn't make sense to use the ORDER BY inside an IN clause, which is true, because Oracle doesn't care about the row order inside an IN clause:"

I tried the SQL statement with WHERE clause and '=' instead of 'IN' and it still threw the error:'missing right parenthesis'.

conclusion 1 :

"Don't use ORDER BY in the WHERE clause subquery" or "Subqueries in the where clause are not allowed to use ORDER BY in Oracle"

Conclusion 2

This case-study also shows the scenario where we should go for JOIN rather than select subquery

take a look at this article: oraclequirks.blogspot.com/2008/01/… — Kirill Leontev
– Kirill Leontev, Commented Jul 14, 2014 at 21:26
wow, very good article, extra information , Thank you so much @beherenow — spiderman
– spiderman, Commented Jul 14, 2014 at 21:39
your conclusion 1 is not entirely correct. subqueries in where clause ARE allowed to use order by, BUT only in case where order by affects result set - for instance, where it provides top-n filtering, like in this fiddle sqlfiddle.com/#!4/d41d8. in other case it throws ora-907. you might also want to take a look at MOS article 731577.1, which I just found - they seem to have a lot of SRs about this so they clarified this ) — Kirill Leontev
– Kirill Leontev, Commented Jul 14, 2014 at 23:18
beherenow, sure I will go through your fiddle and those articles and will either update my conclusion or clarify with you if I come across any queries. Appreciate your effort to review my post even after it is marked as solved. But this is the conclusion shared by both @dasblinkenlight and GordonLinoff — spiderman
– spiderman, Commented Jul 14, 2014 at 23:42

Sergey Kalinichenko · Accepted Answer · 2014-07-14 21:25:41Z

6

The problem is placing ORDER BY in the WHERE clause subquery. SQL syntax does not allow you to order elements of the subquery in the WHERE clause, because it does not change the result of the query overall.

You should move it out to fix the syntax:

SELECT empid  FROM employees WHERE deptid IN
    (
        SELECT deptid FROM department WHERE description LIKE '%application%'
    )
ORDER BY createddate DESC

createddate is not a column in employees table. It exists only in department table

Then you need to join to the department table, and use ORDER BY on one of its columns:

SELECT e.empid
FROM employees e
JOIN department d ON e.deptid = d.deptid
WHERE d.description LIKE '%application%'
ORDER BY d.createddate DESC

edited Jul 14, 2014 at 21:25

answered Jul 14, 2014 at 21:17

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

spiderman Over a year ago

Thank you for that explaining the root-cause. But moving it out is incorrect as createddate is not a column in employees table. It exists only in department table. Still I tried, and got the expected error of 'invalid identifier'

Sergey Kalinichenko Over a year ago

@prash Would you like to order employees on the creation date of the department then?

T.S. Over a year ago

@prash Of course, you can't use identifier from subquery on the outer query. LAs I said, ordering your subquery will not buy you anything anyway

spiderman Over a year ago

Thank you @GordonLinoff . I updated my post with the conclusion

T.S. · Accepted Answer · 2014-07-14 21:17:28Z

2

Obviously, you don't need order by in your subquery and you can remove it and then parenthesis will be just right

SELECT empid  
FROM employees 
WHERE deptid IN (SELECT deptid 
                 FROM department 
                 WHERE description LIKE '%application%');

If you want to apply order by you would have to do it on your outer query

answered Jul 14, 2014 at 21:17

T.S.

19.6k11 gold badges70 silver badges96 bronze badges

2 Comments

spiderman Over a year ago

Thank you for the answer and comments, +1. But since this doesnt address how to place the order by, I am accepting the other as my complete answer. Thank you, though

T.S. Over a year ago

@prash No problem, I was also only concentrating on "why your query is not working" and didn't do anything on solving the issue in general. And I gave +1 to dasblinkenlight for thinking further

Collectives™ on Stack Overflow

Oracle select query with inner select query error

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related