Oracle Order by not working for Subquery from DUAL

Question

Hi all when I executed this query somehow its throwing the following error -

ORA-00907: missing right parenthesis.

But if you remove the order by 1 from SELECT 2 FROM DUAL order by 1 its working.

Did I miss something out here or its ORACLE limitation

SELECT (CASE
        WHEN EXISTS
               (SELECT 1 FROM DUAL) THEN
               (SELECT 4
                FROM dual)
        ELSE
              (SELECT 2 FROM DUAL order by 1 )
    END) AS DELEGATOR FROM dual

Below is a working code with order by 1 removed

SELECT (CASE
        WHEN EXISTS
               (SELECT 1 FROM DUAL) THEN
               (SELECT 4
                FROM dual)
        ELSE
              (SELECT 2 FROM DUAL )
    END) AS DELEGATOR FROM dual

Answer 1

Somehow I already give up but when I change the code to this it somehow works. I applied to my actual query and the result are the expected outcome.

SELECT (CASE WHEN EXISTS (SELECT 1 FROM DUAL) THEN (SELECT 4 FROM dual) ELSE (select * from (SELECT 2 FROM DUAL order by 1 ) where rownum = 1) END) AS DELEGATOR FROM dual

Answer 2

A scalar subquery expression returns exactly one column value from one row. If the expression returned more than one row you would get an error, "ORA-01427: single-row subquery returns more than one row". As it can only have a single value, ordering that value would be meaningless.

The missing-right-parenthesis error doesn't necessarily mean you have unbalanced parentheses, it can indicate other errors that have made the parser give up at the point it expected to see one. Here the parser expected the subquery's closing parenthesis to have come after the FROM DUAL, so it's stopping when it doesn't see one - effectively it doesn't know how to interpret the rest of the statement so it doesn't try (hugely oversimplifying).

An order by clause isn't meaningful in a subquery, and generally isn't allowed; though it is tolerated in some places, such as an inline view, despite still having no effect.

Obviously your example is very contrived, but there is a scenario where you might think you want an order by clause, and that is to get the first value from a result set which you need to be ordered. Based on how limit works in other databases you might try to do something like:

select (
  select object_name from user_objects
  where rownum = 1
  order by created desc
)
from dual

... but that isn't how rownum works, and would also get ORA-00907. If that is what you're doing you would need another layer of subquery (as an inline view now) which can be ordered, and apply the rownum filter to that:

select (
  select object_name from (
    select object_name from user_objects
    order by created desc
  )
  where rownum = 1
)
from dual

... which is now valid.

(Whether you actually need to be doing that in a subquery, rather than through a join, is another matter - your query is hopefully complicated enough to warrant it).

Answer 3

SELECT 2 FROM DUAL order by 1

Firstly, an ORDER BY makes no sense here the query returns just one row.

Secondly, you cannot use an order by in a Scalar Subquery Expression when there is no sense of ordering the rows. Order by would be allowed in certain cases where the outer query expects an ordered set of rows. In your case, there is no question of getting more than a single-row.

Thirdly, it is not a good practice coding as order by 1,2 etc. Instead, use the appropriate column name. You might not come to know if the columns in the select list changes, your result set would be then ordered differently and you will need to make changes at both the places.