×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Two Exceptions on a Function

Two Exceptions on a Function

Two Exceptions on a Function

(OP)
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

CODE

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

CODE

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

RE: Two Exceptions on a Function

(OP)
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

RE: Two Exceptions on a Function

Then would something like

CODE

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:

CODE

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

RE: Two Exceptions on a Function

(OP)
Carp

Thanks for your help

This is what I ended up with

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close