Purging data on master/detail based on detail data

From: Kevin Simon <ksimon_at_fwi.com>
Date: Wed, 28 Apr 1999 16:24:52 -0500
Message-ID: <925334653.765.85_at_news.remarQ.com>

I have submitted a question to Oracle support, but am not having much luck in getting a response. If I've got a master and detail table, and want to purge the master and details only if ALL details meet a certain requirement.

In my example, I've got a master and detail that are linked by company and job number. If all the details for one company/job have a certain status (C for closed), then I want to update a temporary table with the company and job number so I can use that as input to purge a number of tables.

As an example, I give the following sample data. Table: MASTER

     Company      Job
     =======     =====
        1          1
        1          2
        1          3
        2          1
        2          2

Table:  DETAIL
     Company      Job      Line      Status
     =======     =====     ====      ======
        1          1         1          C
        1          1         2          C
        1          1         3          C
        1          1         4          C
        1          2         1          C
        1          2         2          C
        1          2         3          O
        1          3         1          C
        1          3         2
        1          3         3          C
        2          1         1          O
        2          1         2
        2          2         1          C

I would like to create a temporary table which contains two fields: COMPANY and JOB. This will contain the company and job when the following criteria are met: All DETAIL records for a company and job have a STATUS of "C". If there are any DETAIL records which do not have a STATUS of "C" (they may be another status, or may be null), then that COMPANY/JOB should not be on the temporary table.

Using the example data above, the following records will be on the temporary table:
  Company/Job: 1/1, 2/2.
This is because all four detail records for company 1, Job 1 have "C" as the status. The only record for company 2, job 2 has a "C" for the status.

Company/Job 1/2 was disqualified because line 3 has a "O" as the status.
Company/Job 1/3 was disqualified because line 2 had a null status.
Company/Job 2/1 was disqualified because line 1 had an "O" status, and line

2 had a null status.

After I create the temporary table, I would like to use that table as my "Master Purge" table. I would like to purge records off the DETAIL table if the company/job matches that of the temporary table.

My request is two fold. First, how would I write the query to create the temporary table. Second, how would I write the query to delete records off the DETAIL table only if it has a match of company/job on the temporary table.

I hope this example helps you to understand my problem. If you respond to my problem via email, please send it to ksimon_at_fwi.com .

Thanks much for you help. Received on Wed Apr 28 1999 - 23:24:52 CEST