1

I am trying to run the following query, using Oracle 10:

select intel from Intel intel where intel in (select intel from Intel intel where intel.city.name = 'Rome' order by intel.city.name asc)

However, I am getting "ORA-00907: missing right parenthesis". Clearly, this is not because a right parens is missing. I know that this error is sometimes caused by a bug (http://www.dba-oracle.com/sf_ora_00907_missing_right_parenthesis.htm), but my version of Oracle is after the bug has been patched.

Any thoughts on what I could be doing wrong?

Thanks!

Edit: I know the query is redundant, but I specifically need to test a functionality where I am dynamically modifying subqueries at runtime, and the above is written just to test this functionality in the simplest way possible, even if it doesn't add expressivity.

Michael Tontchev
  • 909
  • 8
  • 23
  • This query seems odd. Why can't you just run the part that's inside the parentheses and get rid of the rest? Isn't it redundant? – Mark Leiber May 05 '15 at 00:50
  • Yes, I know it's an odd query, but I can't remove the order by clause. This is part of a larger project where I'm testing a framework I made that dynamically builds queries at runtime, and now I'm testing the subquery order by clause. I wrote the above query because it's the simplest query I can write with a nested subquery that has an order by clause. I completely realize it's redundant and doesn't do anything. But it's a test to see whether I'm adding these order bys correctly. – Michael Tontchev May 05 '15 at 00:52
  • Nope - they're different. All my tables are suffixed by \_TABLE and all my columns are prefixed by table names, like INTEL_. – Michael Tontchev May 05 '15 at 01:01

1 Answers1

2

The Oracle parser produces this particular error for any number of syntax errors - it doesn't really mean you are missing the right parenthesis, but that it isn't where the parser expects it to be.

In this case, the error is because of the ORDER BY in the subquery. It is meaningless to have an ORDER BY clause in an IN subquery, and the parser does not allow it.

If your intent is to have the results sorted, then you want the ORDER BY outside of the subquery:

select intel from Intel intel where intel in (select intel from Intel intel where intel.city.name = 'Rome') order by intel.city.name asc

But based on your comment explaining the purpose, you need to use a different query form to accomplish your test.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
  • Drat, you're right! (Reference: https://openjpa.apache.org/builds/1.2.0/apache-openjpa-1.2.0/docs/manual/jpa_langref.html#jpa_langref_subqueries ). JPQL doesn't allow order by in subqueries! Odd - I'm using QueryDSL, and it lets me shoot myself in the foot by using orderBy(...) in subqueries. Thanks! – Michael Tontchev May 05 '15 at 01:11