Exceeds CPU Limit

User_EBDZC

When I run this report for a particular Principal Investigator, which should return about 6500+ records, I receive a ORA 0094 error. I've worked on it and reduced the filling time in JasperSoft from 1154 seconds to 421 seconds when I run successfully for PIs with smaller reports. This was an existing working report. I added the concatenated visit field and associated fields and the rv_protocol_details view and the rv_sub_calendar view and associated joins. The code looks like this - is there any way it looks like it could be optimized?

select distinct

  protocol_subject_id,

  protocol_no,

  protocol_id,

  get_protocol_staff_names(protocol_id, 'PI')    as pi,

  sequence_number,

  (arm_code || ': ' || visit_name) as visit,

   

      CASE WHEN arm_code is not null and visit_name is not null

    then (arm_code || ': ' || visit_name)

    else visit_name

    end visiit2,

    

  status,

  subject_initials,

  sd_pcs_tracking_id,

  sd_study_spec_id,

  version_no,

  budget_version_no,

  version_date,

  arm_no,

  arm_code,

  arm_description,

  visit_name,

  phase,

  phase_code,

  visit_status,

  sd_ss_tx_cycle_visit_id,

  visit_string,

  visit_description,

  missed_flag,

  na_flag,

  trunc(planned_visit_date)            as planned_visit_date,

  trunc(visit_date)                as visit_date,

  visit_ordinal_value,

  max(invoices)                  as invoices

from

( 

with planned_visit_code as (

 select

  code_id

 from

  pf_code

 where

  description = 'Planned'

 and 

  category = 'VISIT_STATUS'

),

acknowledged_visit_code as (

 select 

  code_id 

 from 

  pf_code 

 where 

  description = 'Acknowledged' 

 and 

  category ='VISIT_STATUS'

),

 invoice_results as (

 select 

  distinct

   opit.protocol_subject_id,

   spt.sd_pcs_tracking_id,

   opi.pcl_invoice_id,

   opi.invoice_no,

   opi.sent_date,

   opm.arm_no,

   sum(opit.cost)    invoice_visit_direct,

   sum(opit.due)    invoice_visit_due,

   opm.visit_id,

   opit.milestone_id

 from

  onc_pcl_invoice    opi,

  onc_pcl_invoice_item opit,

  onc_pcl_milestone   opm,

  sd_pcs_tracking    spt

 where

  opi.pcl_invoice_id = opit.pcl_invoice_id

 and 

  nvl(opi.is_void, 'N') = 'N'

 and 

  opit.protocol_subject_id = spt.protocol_subject_id

 and 

  opit.milestone_id = opm.pcl_milestone_id (+)

 and ( 

    (opit.milestone_id is null and opit.visit_id = spt.sd_pcs_tracking_id) or

    (opit.milestone_id is not null and opm.visit_id = spt.sd_ss_tx_cycle_visit_id and nvl(opm.arm_no, 0) = nvl(spt.arm_no, 0) )

   )

 group by

  opit.protocol_subject_id,

  spt.sd_pcs_tracking_id,

  opi.pcl_invoice_id,

  opi.invoice_no,

  opi.sent_date,

  opm.visit_id,

  opit.milestone_id,

  opm.arm_no

 order by

  opit.protocol_subject_id,

  spt.sd_pcs_tracking_id,

  opi.sent_date

)

select 

 distinct

 pcs.protocol_subject_id,

 sp.protocol_no,

 sp.protocol_id,

 pcs.sequence_number,

 pcs.study_status|| decode(ss.expired_date, null, '', ' (Expired)')|| decode(pcs.survival_status,

  (

    select

      code_id

    from

      pf_code

    where

        category = 'SURVIVAL_STATUS'

      and internal_code = 'LOST_TO_FOLLOW_UP'

  ),

       ' (LTFU)',

       '')                              status,

  upper(substr(ss.subject_first_name, 0, 1))|| upper(substr(ss.subject_middle_name, 0, 1))|| upper(substr(ss.subject_last_name, 0, 1))          as subject_initials,

  spt.sd_pcs_tracking_id,

  spt.sd_study_spec_id,

  sss.version_no,

  spt.budget_version_no|| ' ('|| pbv.version_date|| ')' as budget_version_no,

  pbv.version_date,

  spt.arm_no,

  psa.arm_code,

  psa.arm_description,

  sc.visit_name,

  (sc.arm_code || ': ' || sc.visit_name) as visit,

  f.description phase,

  f.code phase_code,

  case

    when (st.description is not null and st.description = 'Planned' ) then 'Marked as Planned'

    when (spt.visit_date is null and st.description = 'Planned' ) then st.description

    when spt.na_flag = 'Y' then 'NA'

    when spt.missed_flag = 'Y' then 'Missed'

    when coalesce(spt.na_flag, 'N') = 'N' and coalesce(spt.missed_flag, 'N') = 'N' and st.description = 'Acknowledged' then 'Occurred'

    else st.description

  end visit_status,

  spt.sd_ss_tx_cycle_visit_id,

  nvl(cv.revised_visit_string, spt.visit_string) visit_string,

  spt.visit_description,

  spt.missed_flag,

  spt.na_flag,

  spt.planned_visit_date,

  spt.visit_date,

  oncore.spec.get_visit_ordinal_value(spt.sd_ss_tx_cycle_visit_id) visit_ordinal_value,

  case

    when (ir.milestone_id is null and ir.visit_id = spt.sd_pcs_tracking_id ) or

       (ir.milestone_id is not null and ir.visit_id = spt.sd_ss_tx_cycle_visit_id and nvl(ir.arm_no, 0) = nvl(spt.arm_no, 0) ) then ir.invoice_no|| ' ('|| ir.sent_date|| ')'

    else null

  end as invoices

from

 sd_pcs_tracking     spt,

 sd_ss_tx_cycle_visit  cv,

 smrs_pcl_cent_subject  pcs,

 smrs_subject      ss,

 rv_protocol_details   sp,

 --rv_calendar_visit  rcv,

 rv_sub_calendar sc,

 smrs_pcl_step_arm    psa,

 sd_study_spec      sss,

 onc_pcl_budget_version pbv,

 pf_code         f,

 pf_code         st,

 planned_visit_code,

 acknowledged_visit_code, 

 invoice_results     ir

where

 --nvl(sp.ltfu, 'N') = 'N'

--and 

 spt.sd_ss_tx_cycle_visit_id = cv.sd_ss_tx_cycle_visit_id

and 

 spt.protocol_subject_id = pcs.protocol_subject_id

and 

 pcs.subject_no = ss.subject_no

and 

 pcs.protocol_id = sp.protocol_id

and 

 sp.protocol_id in ( 

           select

            protocol_id

          from

            sv_protocol

          where ($P{Department} is null or $P{Department} = department)

           )

and

  ($P{PI} is null or $P{PI} = get_protocol_staff_names(sp.protocol_id,'PI'))

and ($P{pcl_no} is null or sp.protocol_no = $P{pcl_no})

--sp.protocol_no = '2000024788'

--and pcs.protocol_id                   = rcv.protocol_id

and spt.arm_no = psa.arm_no (+)

and spt.sd_ss_tx_cycle_visit_id = sc.sd_ss_tx_cycle_visit_id

--and sc.sd_ss_tx_cycle_visit_id           = rcv.sd_ss_tx_cycle_visit_id (+)

and sp.protocol_id = sss.protocol_id

and pcs.protocol_id = sss.protocol_id

and sp.protocol_id = sc.protocol_id

and spt.sd_study_spec_id = sss.sd_study_spec_id

and pbv.sd_study_spec_id = sss.sd_study_spec_id

and spt.sd_study_spec_id = pbv.sd_study_spec_id

and spt.budget_version_no = pbv.budget_version_no

and spt.visit_status = st.code_id

and st.category = 'VISIT_STATUS'

and spt.phase = f.code_id

and f.category = 'EVENT_TYPE'

and ( 

   oncore.spec.get_visit_phase(spt.sd_ss_tx_cycle_visit_id) = 'OFS' or 

   (

    f.code = 'ONF' and 

    (

     pcs.off_studydate is null or 

     spt.planned_visit_date <= pcs.off_studydate or 

     (pcs.off_studydate is not null and spt.visit_status = acknowledged_visit_code.code_id)

    ) and

    (

     spt.arm_no is null or

     (

     spt.visit_status <> planned_visit_code.code_id or 

     (exists (select 1 from smrs_pcs_step_arm am where am.protocol_subject_id = spt.protocol_subject_id and spt.arm_no = am.arm_no and nvl(off_arm_date, spt.planned_visit_date) >= spt.planned_visit_date))

     )

    )

   )

    or

   (

    (

    oncore.spec.get_visit_phase(spt.sd_ss_tx_cycle_visit_id) = 'ONT' and 

     (

      spt.arm_no is null or 

      (

      spt.visit_status <> planned_visit_code.code_id or 

      (exists( select 1 from smrs_pcs_step_arm am where am.protocol_subject_id = spt.protocol_subject_id and spt.arm_no = am.arm_no and nvl(off_arm_date, spt.planned_visit_date) >= spt.planned_visit_date))

      ) 

     ) 

    )

    and 

    ( 

     spt.visit_status <> planned_visit_code.code_id or

     (

      (pcs.off_treatmentdate is null or spt.planned_visit_date <= pcs.off_treatmentdate ) and

      (pcs.followup_start_date is null or spt.planned_visit_date <= pcs.followup_start_date ) and 

      (pcs.off_studydate is null or spt.planned_visit_date <= pcs.off_studydate)

     )

    )

   ) 

  )

and 

 (

  pcs.status != 'EXPIRED' or 

  (pcs.status = 'EXPIRED' and spt.planned_visit_date <= ss.expired_date)

 )

and 

 ir.protocol_subject_id (+) = pcs.protocol_subject_id                           )

group by 

 protocol_subject_id,

protocol_no,

protocol_id,

sequence_number,

 status,

subject_initials,

sd_pcs_tracking_id,

sd_study_spec_id,

version_no,

budget_version_no,

version_date,

arm_no,

arm_code,

visit_name,

visit,

arm_description,

phase,

phase_code,

visit_status,

sd_ss_tx_cycle_visit_id,

visit_string,

visit_description,

missed_flag,

na_flag,

planned_visit_date,

visit_date,

visit_ordinal_value

order by 

 protocol_no,

 sequence_number,

 protocol_subject_id,

 visit_ordinal_value,

 nvl(visit_date,planned_visit_date)

thatJeffSmith-Oracle

This forum is for questions around 'Oracle SQL Developer' the program, I think you're in the wrong place.