Finding value of latest version 1

[IanWaterman]

This does not look very efficient to me, is there a better way to do this. I am trying to find the Windscreen Excess of the latest version of the policy

select Windscreen_excess from
(
select itm_policy_cde, policy_items_key, itm_version, windscreen_excess, carterms_excess from policy_items 
inner join quote_results_header 
  on policy_items_key = qrh_policy_items_cde and itm_policy_cde = 'REF000000026606'
INNER JOIN quote_output_header
  ON quote_results_header_key = qoh_quote_result_cde
INNER JOIN quote_details_pm qdp
  ON qdt_quote_output_cde = quote_output_header_key
INNER JOIN (select qeb_quote_output_cde,  
                    MAX(case when qeb_windscreen_ind = 1 then qeb_excess_amount else 0 end) windscreen_excess,
                    MAX(case when qeb_car_ind = 1 then qeb_excess_amount else 0 end) carterms_excess
                    from quote_xs_breakdown_pm 
                    group by qeb_quote_output_cde) 
  ON qdt_quote_output_cde =  qeb_quote_output_cde
)
where itm_version = (select max(itm_version) from policy_items 
                    inner join quote_results_header 
                    on policy_items_key = qrh_policy_items_cde and itm_policy_cde = 'REF000000026606'
                    INNER JOIN quote_output_header
                        ON quote_results_header_key = qoh_quote_result_cde
                    INNER JOIN quote_details_pm qdp
                        ON qdt_quote_output_cde = quote_output_header_key
                    INNER JOIN (select qeb_quote_output_cde,  
                        MAX(case when qeb_windscreen_ind = 1 then qeb_excess_amount else 0 end) windscreen_excess,
                        MAX(case when qeb_car_ind = 1 then qeb_excess_amount else 0 end) carterms_excess
                        from quote_xs_breakdown_pm 
                        group by qeb_quote_output_cde) 
                    ON qdt_quote_output_cde =  qeb_quote_output_cde)

Ultimately I then need to convert this into a function replacing the policy ref 'REF000000026606' with an input parameter.

What is the syntax for converting this to a function.

Thank you

Ian

 

[Dagon]

The performance won't necessarily be too bad if there are appropriate indexes, particularly on the policy_cde but also on the joining columns of the other tables. If you are just interested in the windscreen excess, then why are you getting carterms_excess as well? If quote_xs_breakdown_pm only has one row for the value of quote_output_cde and qeb_windscreen_ind=1, then you may not need to do a select max() there.

You could consider using a ROW_NUMBER or RANK function ordering by itm_version descending, but I don't know if it would be much faster. To convert it to a function, you would need something like:

create or replace function my_func(p_pol_code policy_items
.itm_policy_cde%TYPE) return quote_xs_breakdown_pm.qeb_excess_amount%TYPE IS
l_var quote_xs_breakdown_pm.qeb_excess_amount%TYPE;
cursor c_res (p_pol_code policy_items
.itm_policy_cde%TYPE) is
select Windscreen_excess
from
(
select itm_policy_cde, policy_items_key, itm_version, windscreen_excess, carterms_excess from policy_items
inner join quote_results_header
on policy_items_key = qrh_policy_items_cde and itm_policy_cde = p_pol_code
INNER JOIN quote_output_header
ON quote_results_header_key = qoh_quote_result_cde
INNER JOIN quote_details_pm qdp
ON qdt_quote_output_cde = quote_output_header_key
INNER JOIN (select qeb_quote_output_cde,
MAX(case when qeb_windscreen_ind = 1 then qeb_excess_amount else 0 end) windscreen_excess,
MAX(case when qeb_car_ind = 1 then qeb_excess_amount else 0 end) carterms_excess
from quote_xs_breakdown_pm
group by qeb_quote_output_cde)
ON qdt_quote_output_cde = qeb_quote_output_cde
)
where itm_version = (select max(itm_version) from policy_items
inner join quote_results_header
on policy_items_key = qrh_policy_items_cde and itm_policy_cde = p_pol_code
INNER JOIN quote_output_header
ON quote_results_header_key = qoh_quote_result_cde
INNER JOIN quote_details_pm qdp
ON qdt_quote_output_cde = quote_output_header_key
INNER JOIN (select qeb_quote_output_cde,
MAX(case when qeb_windscreen_ind = 1 then qeb_excess_amount else 0 end) windscreen_excess,
MAX(case when qeb_car_ind = 1 then qeb_excess_amount else 0 end) carterms_excess
from quote_xs_breakdown_pm
group by qeb_quote_output_cde)
ON qdt_quote_output_cde = qeb_quote_output_cde);

begin
open c_res(p_pol_code);
fetch c_res into l_var;
if c_res%notfound then
l_var :=NULL;
end if;
close c_res;

return l_var;

end;

For Oracle-related work, contact me through Linked-In.

 

[IanWaterman]

Thanks Dagon

I should have removed carterms. But will need a function for that too.

All the fields used in joins are indexed so from what you are saying speed should not be a problem.

It's a long story about why I am having to do this but basically the developers have made some mistakes, and when a policy is changed the quote_xs_breakdown_pm is not always updated.

So the main policy data could be on say version 6 and excess table only on version 3. So I now have to find the latest version in the excess table and assume that is still correct for the current version of the policy.

Ian