Two Exceptions on a Function 1

[IanWaterman]

I have a function which works 99.99% of the time, however, we have discovered another exception but I can not work out how to add it in.

This is current function

create or replace
Function  FCIM_Start_Of_Risk_Period_RH (Polno Genpolicy.Policy_Key%Type, Eff_Date riskhistory.Rkh_Effective_Date%type)
    Return Date Is
    Ret_Date Date;
    Start_Date Date;
   
Begin
    Begin
        Select Trunc(Start_date) Into Ret_Date
            from
            (Select Add_Months(itm_end_date+1 ,-12) Start_date
             From Riskhistory 
             inner join Policy_items on Rkh_Policy_Cde = itm_Policy_Cde
                                    and rkh_version = itm_version
					                          and itm_item_type = 0  
                                    and itm_status_cde <> 3
                 Where Rkh_Policy_Cde = PolNo
                       And Rkh_Live in ('N', 'Y')
                       And Trunc(Rkh_Effective_Date) <=  Eff_Date
                       And (Lower(Rkh_Description) Like '%inception%'
                           or Lower(Rkh_Description) Like '%renew%'
                           or Lower(rkh_description)Like '%policy migrated%')
                        Order By Rkh_Version Desc)
        where rownum=1;
        return Ret_Date;
  exception
              When no_data_found Then
              Select Trunc(Start_date) Into Ret_Date
              from   
                (select distinct ad.aon_link_policy, ad.aon_main_policy, Add_Months(itm_end_date ,-12) Start_date, Rkh_Version
                from addons ad
                inner join riskhistory on ad.aon_main_policy = rkh_policy_cde
                       --and AD.AON_VERSION = RISKHISTORY.RKH_VERSION
                       And Rkh_Live in ('N', 'Y')
                       And Trunc(Rkh_Effective_Date) <= Eff_Date
                       And (Lower(Rkh_Description) Like '%inception%'
                           or Lower(Rkh_Description) Like '%renew%'
                           or Lower(rkh_description)Like '%policy migrated%')
                inner join Policy_items on Rkh_Policy_Cde = itm_Policy_Cde
                      and rkh_version = itm_version
                      and itm_item_type = 0  
                      and itm_status_cde <> 3
                Where ad.aon_link_policy = PolNo
                Order By Rkh_Version Desc)
	    where rownum=1;
               return ret_date;
 
            When others then raise;
    End;
END;

I need to add another exception so that if Null results in the above the following code kicks in and is my catch all

  When no_data_found Then
                select  Trunc(Pol_Start_Date), Trunc(Pol_Ren_Date) 
                    Into  start_Date, ren_Date
                 from genpolicy where policy_key = polno;
    
                Ret_Date := To_Date(To_Char(start_Date,'DD-MM-')||To_Char(Add_Months(ren_Date,-12),'YYYY'),'DD-MM-YYYY');
               return ret_date;

But when I add in i get a compilation error

Error(47,15): PLS-00483: exception 'NO_DATA_FOUND' may appear in at most one exception handler in this block

Thank you

Ian

 

[IanWaterman]

If it helps I can differentiate between the two existing queries, but I do not know how to apply an if.. then..else or case to trigger the two different queries.

The rule would be

where genpolicy.policy_key = PolNo and genpolicy.Pol_plan_cde = 'MainPolicy then run first query
else run second query.

then I could add my new catch all exceptions query.

Thanks

Ian

 

[carp]

Then would something like

EXCEPTION
   WHEN no_data_found THEN 
      IF (genpolicy.policy_key = PolNo and genpolicy.Pol_plan_cde = 'MainPolicy) THEN 
         run first query 
      ELSE
         run second query.
      END IF
   WHEN OTHERS THEN ...
END;

do the trick for you? You'll need to assign the values from the query to variables before you can use them in the exception, but I think you'll get the idea.
I would also suggest the following structure so you only have one RETURN point:

BEGIN
   BEGIN
     -- main code
   EXCEPTION
     -- exception code
   END;
   RETURN ret_date;
END;

 

[IanWaterman]

Carp

Thanks for your help

This is what I ended up with

create or replace
Function  FCIM_Start_Of_Risk_Period_RH (Polno Genpolicy.Policy_Key%Type, Eff_Date riskhistory.Rkh_Effective_Date%type)
    Return Date Is
    Ret_Date Date;
    Ren_Date Date;
    Start_Date Date;
    PlanCde INT;
   
Begin
    Begin
        Select Trunc(Start_date) Into Ret_Date
            from
            (Select Add_Months(itm_end_date+1 ,-12) Start_date
             From Riskhistory 
             inner join Policy_items on Rkh_Policy_Cde = itm_Policy_Cde
                                    and rkh_version = itm_version
					                          and itm_item_type = 0  
                                    and itm_status_cde <> 3
                 Where Rkh_Policy_Cde = PolNo
                       And Rkh_Live in ('N', 'Y')
                       And Trunc(Rkh_Effective_Date) <=  Eff_Date
                       And (Lower(Rkh_Description) Like '%inception%'
                           or Lower(Rkh_Description) Like '%renew%'
                           or Lower(rkh_description)Like '%policy migrated%')
                        Order By Rkh_Version Desc)
        where rownum=1;
  exception
            When no_data_found Then
              select pol_plan_cde into PlanCde from Genpolicy where policy_key = PolNo;
              
            If planCde <> 656 then 
              Select Trunc(Start_date) Into Ret_Date
              from   
                (select distinct ad.aon_link_policy, ad.aon_main_policy, Add_Months(itm_end_date ,-12) Start_date, Rkh_Version
                from addons ad
                inner join riskhistory on ad.aon_main_policy = rkh_policy_cde
                       --and AD.AON_VERSION = RISKHISTORY.RKH_VERSION
                       And Rkh_Live in ('N', 'Y')
                       And Trunc(Rkh_Effective_Date) <= Eff_Date
                       And (Lower(Rkh_Description) Like '%inception%'
                           or Lower(Rkh_Description) Like '%renew%'
                           or Lower(rkh_description)Like '%policy migrated%')
                inner join Policy_items on Rkh_Policy_Cde = itm_Policy_Cde
                      and rkh_version = itm_version
                      and itm_item_type = 0  
                      and itm_status_cde <> 3
                Where ad.aon_link_policy = PolNo
                Order By Rkh_Version Desc)
	    where rownum=1;
             Else
                select  Trunc(Pol_Start_Date), Trunc(Pol_Ren_Date) 
                    Into  start_Date, ren_Date
                 from genpolicy where policy_key = polno;
                Ret_Date := To_Date(To_Char(start_Date,'DD-MM-')||To_Char(Add_Months(ren_Date,-12),'YYYY'),'DD-MM-YYYY');
            END IF;
          When others then raise;
    End;
    return ret_date;
END;

Thank you

Ian