CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Ehsan
Ehsan

Reputation: 33

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

This Query give an error

ORA-00907: Missing right parenthesis

I can't find any parenthesis problem 

   select 
    (select PRE_DESIG_ID FROM AUTHORIZATION 
         WHERE PROJECT_ID = 5 and PRE_DESIG_ID =48 and 
    ROWNUM=1 order by ID DESC) AS PERPARED_BY
    ,(Select PRE_LAST_DATE From (Select PRE_LAST_DATE From AUTHORIZATION  
    Where PROJECT_ID = 5 and PRE_DESIG_ID = 48 Order By ID Desc) 
    Where ROWNUM = 1) AS 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;

Upvotes: 0

Views: 91

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

You need to convert the derived column's (prepared_by) subquery similar to pre_end_dt's subquery as below

Select 
       (Select pre_desig_id
          From (Select pre_desig_id,
                       row_number() over (order by ID desc) as rn
                  From Authorization
                 Where project_id = 5
                   and pre_desig_id = 48
                 )
         Where rn = 1)  as prepared_by,
       (Select pre_last_date
          From (Select pre_last_date,
                       row_number() over (order by ID desc) as rn
                  From Authorization
                 Where project_id = 5
                   and pre_desig_id = 48
                 )
         Where rn = 1) as 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;

where Order By ID Desc part produces the error, that should be wrapped within an extra subquery, otherwise compiler doesn't allow to use Order By directly as this. i.e. restriction (rownum=1) and using order by are not allowed to work at the same level, they need to exist within outer, and inner select statements respectively.

In fact, it's better to use row_number() window analytic function rather than rownum pseudocolumn, which is untrustable for most cases since it is generated before sorting.

Even if using Where rownum = 1 with order by ID desc for inner select statement is enough for you to use, rather make an habit using row_number() function.

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

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;

Upvotes: 4

Related Questions