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)