sql - ORDER BY with Inner query, giving ORA-00907 missing right parenthesis

Question

I am trying to test a query and getting the ORA-00907 error. I believe that the error is due to the inner query I am using for RUN.RN_RUN_ID.
The inner query by itself works fine and I give the result of the inner query, the outer query is working fine. Any insight into what I am doing wrong, would be much appreciated.

Select
    RELEASES.REL_NAME AS RELEASE_NAME,
    RELEASE_CYCLES.RCYC_NAME AS CYCLE_NAME,
    TESTCYCL.TC_TESTCYCL_ID AS TEST_INSTANCE_ID,
    TEST.TS_NAME AS TEST_CASE_NAME,
    TEST.TS_USER_26 AS TEST_POST_CONDITIONS,
    TEST.TS_USER_25 AS TEST_PRE_CONDITIONS,
    TEST.TS_TEST_ID AS TEST_CASE_ID,
    TEST.TS_DESCRIPTION AS TEST_CASE_DESCRIPTION,
    TESTCYCL.TC_STATUS  AS TEST_ACTUAL_RESULTS,
    TEST.TS_DEV_COMMENTS AS TEST_CASE_COMMENTS,
    TEST.TS_RESPONSIBLE AS TEST_CASE_AUTHOR,
    TEST.TS_CREATION_DATE AS TEST_CASE_CREATION_DATE,
    TESTCYCL.TC_EXEC_DATE AS EXECUTION_DATE,
    TESTCYCL.TC_EXEC_TIME AS EXECUTION_TIME,
    TESTCYCL.TC_ACTUAL_TESTER AS EXECUTED_BY,
    STEP.ST_ID AS STEP_ID,
    STEP.ST_STEP_NAME AS STEP_NUMBER,
    STEP.ST_DESCRIPTION AS STEP_DESCRIPTION,
    STEP.ST_EXPECTED AS EXPECTED_RESULTS,
    STEP.ST_ACTUAL AS ACTUAL_RESULTS,
    STEP.ST_USER_07 AS STEP_COMMENT,
    STEP.ST_STATUS AS STEP_STATUS,
    STEP.ST_STEP_ORDER AS STEP_ORDER,
    STEP.ST_USER_01 AS STEP_TYPE,
    RUN.RN_RUN_ID AS RUN_ID From ((((((RELEASES JOIN RELEASE_CYCLES
    ON RELEASES.REL_ID = RELEASE_CYCLES.RCYC_PARENT_ID 
    AND RELEASES.REL_NAME = 'XYZ') LEFT JOIN CYCLE
    ON RELEASE_CYCLES.RCYC_ID = CYCLE.CY_ASSIGN_RCYC) LEFT JOIN TESTCYCL
    ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID) LEFT JOIN RUN
    ON TESTCYCL.TC_TESTCYCL_ID = RUN.RN_TESTCYCL_ID) LEFT JOIN TEST
    ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID) LEFT JOIN STEP
    ON RUN.RN_RUN_ID = STEP.ST_RUN_ID) WHERE (TEST.TS_TYPE = 'MANUAL' OR 
    TEST.TS_TYPE  = 'QUICKTEST_TEST' OR TEST.TS_TYPE = 'LR-SCENARIO')
    AND RUN.RN_RUN_ID in(Select max(RUN.RN_RUN_ID) From (((((((RELEASES JOIN RELEASE_CYCLES
    ON  RELEASES.REL_ID = RELEASE_CYCLES.RCYC_PARENT_ID AND RELEASES.REL_NAME = 'XYZ') 
    LEFT JOIN  CYCLE ON RELEASE_CYCLES.RCYC_ID = CYCLE.CY_ASSIGN_RCYC)
    LEFT JOIN TESTCYCL ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID)
    LEFT JOIN RUN ON TESTCYCL.TC_TESTCYCL_ID = RUN.RN_TESTCYCL_ID)
    LEFT JOIN TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID)
    LEFT JOIN STEP ON RUN.RN_RUN_ID = STEP.ST_RUN_ID)
    LEFT JOIN LINK ON STEP.ST_ID  = LINK.LN_ENTITY_ID AND LINK.LN_ENTITY_TYPE = 'STEP')
    WHERE (TEST.TS_TYPE = 'MANUAL' OR TEST.TS_TYPE = 'QUICKTEST_TEST' OR 
    TEST.TS_TYPE = 'LR-SCENARIO')AND TEST.TS_TEST_ID =145965
    ORDER BY TESTCYCL.TC_TESTCYCL_ID)
    ORDER BY TESTCYCL.TC_TESTCYCL_ID,STEP.ST_STEP_ORDER

Answer 1

The coding style and unnecessary nested brackets make this really hard to read and interpret. But it also does help that, contrary to some comments, an ORA-00907 doesn't always mean an uneven number of parentheses, it can indicate a more general syntax error that's caused the parser to bail out. In this case it isn't very helpful.

The problem is the order by clause on the penultimate line, within the subquery you're comparing against with in:

...
AND RUN.RN_RUN_ID in(Select max(RUN.RN_RUN_ID) From (((((((RELEASES JOIN RELEASE_CYCLES
...
TEST.TS_TYPE = 'LR-SCENARIO')AND TEST.TS_TEST_ID =145965
ORDER BY TESTCYCL.TC_TESTCYCL_ID)
ORDER BY TESTCYCL.TC_TESTCYCL_ID,STEP.ST_STEP_ORDER

The final ordering is obviously allowed, but in that subquery it is not. So it should end:

...
TEST.TS_TYPE = 'LR-SCENARIO')AND TEST.TS_TEST_ID =145965)
ORDER BY TESTCYCL.TC_TESTCYCL_ID,STEP.ST_STEP_ORDER

I can't test that as I don't have your schema, but a simpler demo might help demonstrate:

select d1.dummy
from dual d1
where d1.dummy in (
  select dummy
  from dual d2
  order by d2.dummy
)
order by d1.dummy;

Error at Command Line : 6 Column : 3
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

Removing the inner order by:

select d1.dummy
from dual d1
where d1.dummy in (
  select dummy
  from dual d2
)
order by d1.dummy;

DUMMY
-----
X     

It is expecting to see a ) instead of that order by, so the error does make some sense, once you know what it wrong; but it doesn't really help you narrow it down.

Incidentally, this is referenced in Oracle support document 731577.1:

Getting ORA-00907: missing right parenthesis when using an ORDER BY clause in a subquery. When the ORDER BY clause is removed the query runs without error.

...
This is expected behavior per Bug 4944718 ORDER BY in a subquery shouldn't work, since the order of the rows is passed to the outer query and has no impact.

It is allowed/ignored in an inline view, but not in a nested subquery. (Though there may be exceptions where it still doesn't throw an error...)