Oracle selects a query with an internal selection query error

Question

I get an SQL error and try to decide any pointers would be helpful,

// this is done

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

// this is done

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

but below the request causes an 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 brackets 00907. 00000 - "Missing correct parenthesis"

Update: 07/14:

Update using the exact solution from @dasblinkenlight:

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

Many concepts are well explained in this article - http://oraclequirks.blogspot.com/2008/01/ora-00907-missing-right-parenthesis.html

"ORA-00907: the right bracket is missing. Obviously, when you receive a message, for example, the first reaction is probably to check the bracket was missing, but unfortunately there are no brackets in this statement.

To shorten it, the obscure syntax quirk is summarized as follows: do not use ORDER BY inside the IN subquery.

Now you can argue that it really doesn't make sense to use BY ORDER inside an IN clause, which is true because Oracle doesn't care about the order of the lines inside the IN clause:

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

conclusion 1 :

"Do not use ORDER BY in a WHERE clause subquery" or "Subqueries in a where clause cannot use ORDER BY in Oracle"

Conclusion 2

This case study also shows a scenario in which we should go to JOIN and not select a subquery

Answer 1

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

You must move it to correct the syntax:

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

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

Then you need to join the department table and use ORDER BY in one of your 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 

Answer 2

Obviously, you do not need order by in your subquery, and you can remove it, and then the brackets will be in order.

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

If you want to apply order by , you will need to do this according to your external request