Order by with Inner Query, Giving Ora-00907 Missing Right Parenthesis

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

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

ORA-00907: missing right parenthesis in subquery when using order by and rownum = 1

I solved by separate the query for rownum and move the number where to upper query. At least the query works well and give correct result. But maybe this query can be improved for performance.

Select ...,
  A.number,
  (select K.created_by_user_id from
                 (SELECT L.created_by_user_id
                   FROM user_content L
                  WHERE ( 
                          L.content LIKE   '%message%' 
                          OR L.content LIKE   '%response%'
                        ) 
                        ORDER BY created_by_date
                        ) K
                 where ROWNUM = 1 AND L.number = A.number) as first_user
  FROM ...

ORA-00907 Missing right parenthesis issue - select with order by inside insert query

Both the current answers ignore the fact that using order by and rownum in the same query is inherently dangerous. There is absolutely no guarantee that you will get the data you want. If you want the first row from an ordered query you must use a sub-query:

insert into my_tbl ( col1, col2 )
select data, 'more data'
  from ( select data
           from fir_tabl
          where id = 1
          order by created_on desc )
 where rownum = 1
       ;

You can also use a function like rank to order the data in the method you want, though if you had two created_on dates that were identical you would end up with 2 values with rnk = 1.

insert into my_tbl ( col1, col2 )
select data, 'more data'
  from ( select data
              , rank() over ( order by created_on desc ) as rnk
           from fir_tabl
          where id = 1)
 where rnk = 1
       ;

ORA-00907: Missing right parenthesis ,maybe problem with SubQuery

Your problem is this:

ORDER BY id DESC

You can't use ORDER BY in a subquery that acts as a column expression. If you remove it the error you're facing will go away.

But, I would prefer you rewrite your query using a with clause, since both of your sub-query expression fetches the same row.

WITH auth AS ( SELECT *
                 FROM ( SELECT pre_desig_id AS perpared_by,
                    pre_last_date AS pre_end_dt
                        FROM authorization
                      WHERE project_id = 5
          AND pre_desig_id = 48 ORDER BY id DESC )
 WHERE ROWNUM = 1 )
SELECT a.perpared_by,a.pre_end_dt
  FROM authorization au
  LEFT JOIN project p ON au.project_id = p.project_id
  LEFT JOIN designation d ON au.au_desig_id = d.desigid
 CROSS JOIN auth a;

ORA-00907: Missing Right Parenthesis on a left join of two subqueries

If you remove AS it works:

   SELECT table_name, column_name, x.constraint_name, constraint_type
      FROM (SELECT a.table_name, a.column_name, a.constraint_name
              FROM all_cons_columns  a
             WHERE a.owner = '[my_user]'
               AND a.table_name NOT LIKE 'APEX%'
               AND a.constraint_name NOT LIKE 'BIN%'
             ORDER BY a.table_name) x
              LEFT JOIN (SELECT b.constraint_name, b.constraint_type
                    FROM all_constraints b
                   WHERE b.owner = '[my_user]'
                     AND b.table_name NOT LIKE 'APEX%'
                     AND b.constraint_name NOT LIKE 'BIN%'
                   ORDER BY b.constraint_name) y
          ON x.constraint_name = y.constraint_name

but I suppose this query might be shorter and without left join, because it's always matches:

select x.table_name,
       x.column_name,
       x.constraint_name,
       y.constraint_type
  from all_cons_columns x
  join all_constraints y
    on x.constraint_name = y.constraint_name
 where x.owner = '[my_user]'
   and x.owner = y.owner
   and x.table_name NOT LIKE 'APEX%'
   and x.constraint_name NOT LIKE 'BIN%'
 order by x.table_name, x.constraint_name

Sub query ORA-00907: missing right parenthesis

Remove ORDER BY from

(SELECT t4.TIN FROM table4 t4, table5 TRA WHERE .... ORDER BY t4.TIN ASC)

Illustration:

SQL> select count(*) from emp
  2  where deptno in (select deptno from dept order by dname);
where deptno in (select deptno from dept order by dname)
                                         *
ERROR at line 2:
ORA-00907: missing right parenthesis

SQL> select count(*) from emp
  2  where deptno in (select deptno from dept);

  COUNT(*)
----------
        14

SQL>

---

Related Topics