CODEWITHSUNDEEP
sqloracle-database
dikunj prajapati
dikunj prajapati

Reputation: 1

ORA-00907: missing right parenthesis --- Inner Query Execution

select *  from USER_SCHEDULER_JOB_RUN_DETAILS 
 where substr(log_date,1,9) in (
     select substr(log_date,1,9) 
       from USER_SCHEDULER_JOB_RUN_DETAILS 
      where rownum <= 1 ORDER BY LOG_DATE DESC);

When I am executing above query I am getting below error. 

ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:   
*Action: 
Error at Line: 7 Column: 55

I tried to execute inner query it is executing but not sure why it is giving me error while executing whole query.

Upvotes: 0

Views: 162

Answers (2)

B. Khan
B. Khan

Reputation: 105

Extra information was contained in the parentheses. ORA-00907

You can use this query as your expectation

select *
  from USER_SCHEDULER_JOB_RUN_DETAILS
 where substr(log_date, 1, 9) in
       (select MAX(substr(log_date, 1, 9)) over(ORDER BY LOG_DATE desc)
          from USER_SCHEDULER_JOB_RUN_DETAILS
         where rownum <= 1);

Upvotes: 0

JAY G
JAY G

Reputation: 549

Don't use order by in the inner query. try this: 

select * from USER_SCHEDULER_JOB_RUN_DETAILS 
where substr(log_date,1,9) in 
(select substr(log_date,1,9) from USER_SCHEDULER_JOB_RUN_DETAILS where rownum <= 1 );

Upvotes: 1

Related Questions