CODEWITHSUNDEEP
sqloracle-databaseoracle11g
JustToKnow
JustToKnow

Reputation: 823

Trying to get the latest execution of a concurrent (oracle 11g)

I am trying to obtain the latest execution of a specific concurrent; i am connecting to an Oracle 11g database.

My query works just fine but for some reason i was not able to obtain only the latest execution, i mean the request_id

This my query:

select nam.user_concurrent_program_name program,
       con.request_id "request id",
       to_char(con.actual_start_date, 'dd/mm/yyyy hh24:mi') start,
       to_char(con.actual_completion_date, 'dd/mm/yyyy hh24:mi') end,
       trunc((con.actual_completion_date - con.actual_start_date) * 1440, 2) time_mm,
       con.status_code status,
       us.user_name user,
       con.oracle_process_id,
       con.oracle_session_id,
       con.argument_text
  from fnd_concurrent_programs_tl nam,
       fnd_concurrent_requests    con,
       fnd_user                   us
 where con.concurrent_program_id = nam.concurrent_program_id
   and con.requested_by = us.user_id
   and con.actual_start_date >= to_date('20/11/2019', 'dd/mm/rrrr')
   and con.actual_start_date < to_date('23/11/2019', 'dd/mm/rrrr') + 1
   and nam.user_concurrent_program_name like 'report concurrent sales'
   and co.request_id =
       (select max(con.request_id)
          from fnd_concurrent_requests can
         where con.request_id = can.request_id);

The problem is that i am getting several rows and i only want the latest row, with the highest request_id.

Could you please help me?

Upvotes: 1

Views: 155

Answers (1)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65363

You can use row_number() analytic function with descending request_id (assuming request_id is a primary key column as being an identity, no tie(repeated column values among rows) will occur):

select program, "request id", "start", "end", "request id", time_mm, status, "user", 
       oracle_process_id, oracle_session_id, argument_text
  from
  (
    select nam.user_concurrent_program_name program,
           con.request_id "request id",
           to_char(con.actual_start_date,'dd/mm/yyyy hh24:mi') "start",
           to_char(con.actual_completion_date,'dd/mm/yyyy hh24:mi') "end",
           trunc((con.actual_completion_date-con.actual_start_date)*1440,2) time_mm,
           con.status_code status,
           us.user_name "user",
           con.oracle_process_id,
           con.oracle_session_id,
           con.argument_text,
           row_number() over (order by con.request_id desc) as rn
      from fnd_concurrent_programs_tl nam
      join fnd_concurrent_requests con
        on con.concurrent_program_id=nam.concurrent_program_id
      join fnd_user us
        on con.requested_by = us.user_id
     where con.actual_start_date >= date'2019-11-20' 
       and con.actual_start_date <  date'2019-11-23' + 1
       and nam.user_concurrent_program_name like  'report concurrent sales'
  )  
where rn = 1

P.S. Pay attention for reserved keywords such as user and end which are wrapped up with quotes.

Edit ( for the new case ) : You can convert the where condition for the subquery to :

 where con.actual_start_date >= date'2019-11-20' 
   and con.actual_start_date <  date'2019-11-23' + 1
   and ( nam.user_concurrent_program_name in 
                                            ('report concurrent sales',
                                             'report concurrent customers',
                                             'report concurrent sunday') )

and add partition by clause if you want to have one row for each user_concurrent_program_name seperately as 

row_number() 
over (partition by nam.user_concurrent_program_name order by con.request_id desc) as rn

Upvotes: 1

Related Questions