Superdooperhero Superdooperhero - 4 years ago 87
SQL Question

Oracle repeated function calls in select and where clause

I have the following Oracle SQL query

select /*+ORDERED */ payr.payroll_name payroll_name,
nvl(papf.employee_number, papf.npw_number) employee_number,
nvl
(
xxpay_util.safe_to_number
(
xxpay_util_element.get_run_result
(
p_business_group_id => paaf.business_group_id, -- in number
p_payroll_id => paaf.payroll_id, -- in number
p_payroll_action_id => null, -- in number,
p_start_period => mon.real_month_start, -- in date
p_end_period => mon.real_month_end, -- in date
p_element_name_regex => '^Disability Cover ER$', -- in varchar2 '^Disability Cover ER Opt Out$'
p_input_value => 'Pay Value', -- in varchar2
p_assignment_id => paaf.assignment_id, -- in number
p_aggregation => 'sum', -- in varchar2
p_round => 2 -- in number default 2
)
),
0
) disability_benefit,
nvl
(
xxpay_util.safe_to_number
(
xxpay_util_element.get_run_result
(
p_business_group_id => paaf.business_group_id, -- in number
p_payroll_id => paaf.payroll_id, -- in number
p_payroll_action_id => null, -- in number,
p_start_period => mon.real_month_start, -- in date
p_end_period => mon.real_month_end, -- in date
p_element_name_regex => '^Death Cover ER Opt Out$', -- in varchar2
p_input_value => 'Pay Value', -- in varchar2
p_assignment_id => paaf.assignment_id, -- in number
p_aggregation => 'sum', -- in varchar2
p_round => 2 -- in number default 2
)
),
0
) death_cover,
nvl
(
xxpay_util.safe_to_number
(
xxpay_util_element.get_run_result
(
p_business_group_id => paaf.business_group_id, -- in number
p_payroll_id => paaf.payroll_id, -- in number
p_payroll_action_id => null, -- in number,
p_start_period => mon.real_month_start, -- in date
p_end_period => mon.real_month_end, -- in date
p_element_name_regex => '^Funeral Cover ER$', -- in varchar2 '^Funeral Cover ER Opt Out$'
p_input_value => 'Pay Value', -- in varchar2
p_assignment_id => paaf.assignment_id, -- in number
p_aggregation => 'sum', -- in varchar2
p_round => 2 -- in number default 2
)
),
0
) funeral_cover,
ppos.actual_termination_date termination_date
from (
select ptp.payroll_id,
min(ptp.start_date) real_month_start,
max(ptp.end_date) real_month_end
from per_time_periods ptp
where decode(ptp.prd_information_category, 'ZA', to_char(ptp.pay_advice_date, 'yyyymm'), to_char(ptp.regular_payment_date, 'yyyymm')) = '201612'
group by ptp.payroll_id
) mon,
pay_all_payrolls_f payr,
per_all_assignments_f paaf,
per_periods_of_service ppos,
per_people_f papf,
per_business_groups pbg,
hr_all_organization_units haou,
per_jobs pj,
pay_cost_allocation_keyflex pcak
where payr.payroll_id = mon.payroll_id
and mon.real_month_end between payr.effective_start_date and payr.effective_end_date
and paaf.payroll_id = mon.payroll_id
and paaf.assignment_type = 'E'
and paaf.primary_flag = 'Y'
and paaf.effective_start_date =
(
select max(paaf2.effective_start_date)
from per_all_assignments_f paaf2
where paaf2.assignment_id = paaf.assignment_id
and paaf2.assignment_type = 'E'
and paaf2.primary_flag = 'Y'
and paaf2.payroll_id = mon.payroll_id
and mon.real_month_start <= paaf2.effective_end_date
and mon.real_month_end >= paaf2.effective_start_date
)
and ppos.period_of_service_id = paaf.period_of_service_id
and mon.real_month_start <= nvl(ppos.actual_termination_date, to_date('31/12/4712', 'dd/mm/yyyy'))
and mon.real_month_end >= ppos.date_start
and papf.person_id = paaf.person_id
and papf.effective_start_date =
(
select max(papf2.effective_start_date)
from per_all_people_f papf2
where papf2.person_id = paaf.person_id
and mon.real_month_start <= papf2.effective_end_date
and mon.real_month_end >= papf2.effective_start_date
)
and pbg.business_group_id = paaf.business_group_id
and haou.organization_id (+) = paaf.organization_id
and pj.job_id (+) = paaf.job_id
and pcak.cost_allocation_keyflex_id (+) = haou.cost_allocation_keyflex_id
and
(
nvl
(
xxpay_util.safe_to_number
(
xxpay_util_element.get_run_result
(
p_business_group_id => paaf.business_group_id, -- in number
p_payroll_id => paaf.payroll_id, -- in number
p_payroll_action_id => null, -- in number,
p_start_period => mon.real_month_start, -- in date
p_end_period => mon.real_month_end, -- in date
p_element_name_regex => '^Disability Cover ER$', -- in varchar2 -- Disability Cover ER Opt Out
p_input_value => 'Pay Value', -- in varchar2
p_assignment_id => paaf.assignment_id, -- in number
p_aggregation => 'sum', -- in varchar2
p_round => 2 -- in number default 2
)
),
0
)
+
nvl
(
xxpay_util.safe_to_number
(
xxpay_util_element.get_run_result
(
p_business_group_id => paaf.business_group_id, -- in number
p_payroll_id => paaf.payroll_id, -- in number
p_payroll_action_id => null, -- in number,
p_start_period => mon.real_month_start, -- in date
p_end_period => mon.real_month_end, -- in date
p_element_name_regex => '^Death Cover ER Opt Out$', -- in varchar2
p_input_value => 'Pay Value', -- in varchar2
p_assignment_id => paaf.assignment_id, -- in number
p_aggregation => 'sum', -- in varchar2
p_round => 2 -- in number default 2
)
),
0
)
+
nvl
(
xxpay_util.safe_to_number
(
xxpay_util_element.get_run_result
(
p_business_group_id => paaf.business_group_id, -- in number
p_payroll_id => paaf.payroll_id, -- in number
p_payroll_action_id => null, -- in number,
p_start_period => mon.real_month_start, -- in date
p_end_period => mon.real_month_end, -- in date
p_element_name_regex => '^Funeral Cover ER$', -- in varchar2 -- Funeral Cover ER Opt Out
p_input_value => 'Pay Value', -- in varchar2
p_assignment_id => paaf.assignment_id, -- in number
p_aggregation => 'sum', -- in varchar2
p_round => 2 -- in number default 2
)
),
0
)
) != 0
and nvl
(
xxpay_util_element.get_element_entry
(
p_business_group_id => paaf.business_group_id, -- in number
p_start_period => mon.real_month_start, -- in date
p_end_period => mon.real_month_end, -- in date
p_element_name_regex => '^Retirement Fund Opt In$', -- in varchar2
p_input_value => 'Opt In or Out', -- in varchar2
p_assignment_id => paaf.assignment_id, -- in number
p_aggregation => 'none', -- in varchar2
p_round => 0 -- in number default 2
),
'$XXX$'
) in ('NC', 'OUT')
and exists
(
select 1
from pay_payroll_actions ppap,
pay_assignment_actions paap,
pay_action_interlocks pai,
pay_assignment_actions paar,
pay_payroll_actions ppar,
per_time_periods ptp
where ppap.action_type in ('P', 'U')
and ppap.action_status = 'C'
and paap.payroll_action_id = ppap.payroll_action_id
and paap.action_status = 'C'
and pai.locking_action_id = paap.assignment_action_id
and paar.assignment_action_id = pai.locked_action_id
and ppar.payroll_action_id = paar.payroll_action_id
and ppar.action_type in ('R', 'Q')
and ptp.time_period_id = ppar.time_period_id
and decode(ptp.prd_information_category, 'ZA', to_char(ptp.pay_advice_date, 'yyyymm'), to_char(ptp.regular_payment_date, 'yyyymm')) = '201612'
and paap.assignment_id = paaf.assignment_id
and paar.assignment_id = paaf.assignment_id
)
order by pcak.segment2, papf.employee_number, paaf.assignment_number


Is there a way to rewrite the query so that the function calls from the select clause (
xxpay_util_element.get_run_result
) that is repeated in the where clause are not repeated and only called once?

Answer Source

you can try creating this sql as subquery and add where clause in parent query :

Select * from (
   select  
         xxpay_util_element.get_run_result as get_run_result_1, --only fetch values here
          .......
   from .....

 )
 where get_run_result_1 ......... --add where clause here
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download