Ora-00907 Missing Right Parenthesis Issue - Select with Order by Inside Insert Query

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

Error SQL: ORA-00907: missing right parenthesis

Your no.of columns in insert does not matches the no.of columns in values field.
Try This..

INSERT INTO TABLE_B (car_id, user, book, msdin)
select 11, user, book, msdin from TABLE_A ;

ora -00907 -missing right parenthesis

I'm not sure that placing the entire select clause inside parentheses is valid. I would have written your query as:

INSERT INTO table2 (column1, column2, ..., columnN)
SELECT column1, column2, ..., columnN
FROM table1
WHERE <condition>;

ORA-00907: missing right parenthesis during inserting into table

('1995-06-02','yyyy-mm-dd')

should be

TO_DATE('1995-06-02','yyyy-mm-dd')

You are not entering a date, you are entering a VARCHAR2 enclosed in parentheses which Oracle doesn't seem to handle.

The VARCHAR2 alone might work and get implicitly converted to DATE if your session is configured correctly.
It is better to not rely on this kind of configuration and do the conversion explicitly using the TO_DATE function.

Update query missing right parenthesis

You can't have an orderby in that statement. Modify it like so to get around the restriction. Also, if you want to return the correct record, I would suggest moving the rownum outside to the second select. Rownum will be incorrect in the inner select since it's applied before the orderby. If you filter by the rownum in the inner select, it's hit or miss whether or not you'll select the max ID, which is what it looks like you want.

UPDATE CONVERTED T1 
   SET PARENTID = (
    SELECT ID FROM (
        SELECT ID FROM 
        CONVERTED T2 WHERE T2.ID < T1.ID 
        AND T1.PREVOBJNUM = T2.OBID 
        ORDER BY ID DESC)
    WHERE ROWNUM = 1 
    )

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

---

Related Topics