DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: BA needs coding help!!

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    BA needs coding help!!

    Hi,

    I'm a Business Analyst trying to get my head round some SQL.
    I'm no coder, but I'm willing to learn.
    I'd greatly appreciate any coding gurus out there who are willing to help me out.

    Here's my scenario:

    3 Tables with hierarchy

    MORTGAGE (parent of - one to many)
    APPLICATION (parent of - one to many)
    CASE_OWNER

    So a MORTGAGE can have many APPLICATIONS
    APPLICATION can have many CASE_OWNERS

    MORTGAGE joins to APPLICATION on MORT_NO,APP_NO
    APPLICATION joins to CASE_OWNER on MORT_NO,APP_NO

    I need code which does the following:

    If I have many applications hanging off a mortgage I want to delete ALL applications (for archiving purposes)

    BUT I can't delete ANY application for that mortgage if ANY application has a stage no > 20
    OR I can't delete ANY application for that mortgage if ANY application is less than a year old


    Kinda:
    Do Not Delete Any APPLICATION for this MORTGAGE
    Where CASE_OWNER.STAGE_NO > 20
    Or APPLICATION.APP_DATE date is within last 12 months

    I know that this is going to have a GROUP BY based on on MORTGAGE
    but I'm struggling!!

    Any help would be great. Ideally, if you could code it, I can then look at the code and see what's going on.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    I would have thought that one case owner can own many applications and each application can own many mortgages. Hence I the person managing the mortgage process gets an application from every borrower and each borrower can have more than one loan. There was a time when people would buy a house and to avoid PMI (private mortgage insurance) they would get a loan for 80% of what they pay for the house at the "good" rate and then get a second mortgage for a slightly higher rate.

    Anyway here is some code that will do what you want to a single mortgage application. When deleting you don't need to aggregate the data, you don't even need to join the data, except to find out that the data you want to delete can be deleted. So if you want to delete all of the mortgages over a certain date that have been processed you still need three delete statements and each one has to filter out applications with stage > 20 and app_date greater than sysdate-365. That code is also below. Your welcome!

    Code:
    create or replace purge_mortgages ( p_mort_no in number, p_app_no in number )
    as
    
       function cant_delete_mortgage ( p_mort_no in number, p_app_no in number )
       return boolean
       as
          v_record_count NUMBER := 0;
       begin
          select count(*)
            into v_record_count
            from application app
           inner join case_owner cas
              on app.mort_no  = cas.mort_no
             and app.app_no   = cas.app_no
           where app.mort_no  = p_mort_no
             and app.app_no   = p_app_no
             and cas.stage    > 20
             and app.app_date > SYSDATE-365;
    
         if v_record_count > 0
         then return true
         else return false
         END IF;
       end cant_delete_mortgage;
    begin
       if cant_delete_mortgage ( p_mort_no, p_app_no )
       then
          raise_application_error(-20012, 
            'The mortgage application for mortgage id '||p_mort_no||chr(10)||
            ' and applcation id '||p_app_no||chr(10)||
            ' can not be deleted due to the newness of the application or the stage being too high';
       END IF;
    
       delete case_owner
        where mort_no = p_mort_no
          and app_no  = p_app_no;
    
       delete application
        where mort_no = p_mort_no
          and app_no  = p_app_no;
    
       delete mortgage
        where mort_no = p_mort_no
          and app_no  = p_app_no;
    
       COMMIT;
    END ;
    /
    Code:
    create global temporary table mortgages_to_delete 
           ( mort_no number, app_no number) 
        ON COMMIT THEN DELETE ROWS;
    
    INSERT INTO mortgages_to_delete
          select mort_no, app_no
            from application app
           inner join case_owner cas
              on app.mort_no  = cas.mort_no
             and app.app_no   = cas.app_no
           where app.mort_no  = p_mort_no
             and app.app_no   = p_app_no
             and cas.stage    > 20
             and app.app_date > sysdate-365;
    
       delete case_owner
        where mort_no = p_mort_no
          and app_no  = p_app_no;
          and ( mort_no, app_no ) not in
              ( select mort_no, app_no
                  from mortgages_to_delete );
    
       delete application
        where mort_no = p_mort_no
          and app_no  = p_app_no
          and ( mort_no, app_no ) not in
              ( select mort_no, app_no
                  from mortgages_to_delete );
    
       delete mortgage
        where mort_no = p_mort_no
          and app_no  = p_app_no
          and ( mort_no, app_no ) not in
              ( select mort_no, app_no
                  from mortgages_to_delete );
    
    COMMIT;

  3. #3
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Thanks Gandolf for your excellent input. I will peruse the code and your comments and see if I can get to grips with it!!

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    If you want to learn PL/SQL try reading PL/SQL Best Practices by Steven Feuerstein.
    It was first published over a decade ago, it has had some updates since, but it is the
    best way to learn PL/SQL.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width