带内部查询的ORDER BY，给出ORA-00907缺少右括号 [英] ORDER BY with Inner query, giving ORA-00907 missing right parenthesis

问题描述

我正在尝试测试查询并出现ORA-00907错误.我认为该错误是由于我用于RUN.RN_RUN_ID的内部查询造成的.
内部查询本身可以正常工作，而我给出内部查询的结果，外部查询可以正常工作.任何对我做错事情的见解，将不胜感激.

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

推荐答案

编码风格和不必要的嵌套括号使它很难阅读和解释.但这也确实有助于，与某些评论相反，ORA-00907并不总是意味着括号的数量不均匀，它可以指示更普遍的语法错误，导致语法分析器无法使用.在这种情况下，它不是很有帮助.

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.

问题是倒数第二行的order by子句，在与in进行比较的子查询中:

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"

卸下内部order by:

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

DUMMY
-----
X     

期望看到的是)而不是order by，因此，一旦您知道出了什么问题，该错误的确有意义.但这并不能真正帮助您缩小范围.

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.

偶然地，在Oracle支持文档731577.1中对此进行了引用:

Incidentally, this is referenced in Oracle support document 731577.1:

在子查询中使用ORDER BY子句时获取ORA-00907: missing right parenthesis.删除ORDER BY子句后，查询将运行而没有错误.

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.

...
根据错误4944718的预期行为 子查询中的ORDER BY无效，因为行的顺序被传递到外部查询，并且没有影响.

...
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...)

这篇关于带内部查询的ORDER BY，给出ORA-00907缺少右括号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！