Missing right parenthesis in Oracle query

Question

I am trying to find, for shop S9, the friend of the user who posted the most number of reviews for it.

I have the following query:

select friend_id 
  from friends 
 where user_id = ( select author_id 
                     from posted_reviews_tab   
                    where rownum <= 1 
                      and reviewid in ( select w.reviews.reviewid 
                                          from wall_tab w  
                                         where w.shopid = 'S9' ) 
                    group by author_id 
                    order by count(author_id) desc );

I get the following error:

SQL Error: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"

I've added whitespace to your query, it makes it easier to read (for me) and shows you a lot more easily where there might be a problem. Your GROUP BY, ORDER BY with the rownum predicate is almost certainly not returning what you think it is. — Ben
Can you share the table structure? It will help constructing query faster. — Darshan Mehta

mrcaramori mrcaramori · Accepted Answer · 2013-06-12T16:24:58

There is a typo in the select:

select friend_id 
  from friends 
 where user_id = ( select author_id 
                     from posted_reviews_tab   
                    where rownum <= 1 
                                        --w.reviews.reviewid is wrong
                      and reviewid in ( select w.reviews.reviewid 
                                          from wall_tab w  
                                         where w.shopid = 'S9' ) 
                    group by author_id 
                    order by count(author_id) desc );

If that is not a typo, you will have to make a join to arrive at '.reviews' since that syntax is not possible.

Missing right parenthesis in Oracle query

1 Answers