Validating One Table Against Another 1

[deepgrewal22]

I am working on a project that must validate the records from one table to another.

(1) All records from a table (raw_rec) must be validated against another table to ensure against typos and bad data.
(2) The validated records that are error-free must be placed into a table for valid records (valid_rec).
(3) The records that failed to validate due to errors must be placed into a table for invalid records (invalid_rec).

Here are the table attrributes which are COMMON for each table:

emp_id, last_name, first_name, hours_per_week

**NOTE: emp_id and last_name are the fields to validate.

I HAVE TO EMBED THIS CODE INTO A VISUAL BASIC PROGRAM AND PREFER AN APPROACH THAT USES SQL.

Deep Grewal
"Microsoft Works" - oxymoron

 

[SantaMufasa]

Deep,

Here is code that does what you want, I believe:

SQL> select * from raw_rec;

    EMP_ID LAST_NAME            FIRST_NAME           HOURS_PER_WEEK
---------- -------------------- -------------------- --------------
         1 Doe                  John                             40
         2 Doh                  Jane                             40
         3 Doh                  Gerry                            30

SQL> select * from emp_rec;

    EMP_ID LAST_NAME            FIRST_NAME           HOURS_PER_WEEK
---------- -------------------- -------------------- --------------
         1 Doe                  John                             40
         2 Doe                  Jayne                            40
         3 Doe                  Gerald                           30

SQL> 
SQL> select * from raw_rec y
  2  where exists (select 'x'
  3                  from (select emp_id, last_name from raw_rec
  4                        minus
  5                        select emp_id, last_name from emp_rec) x
  6                 where x.emp_id = y.emp_id)
  7  /

    EMP_ID LAST_NAME            FIRST_NAME           HOURS_PER_WEEK
---------- -------------------- -------------------- --------------
         2 Doh                  Jane                             40
         3 Doh                  Gerry                            30

SQL> select * from raw_rec y
  2   where exists (select 'x'
  3                  from (select emp_id, last_name from raw_rec
  4                        intersect
  5                        select emp_id, last_name from emp_rec) x
  6                 where x.emp_id = y.emp_id)
  7  /

    EMP_ID LAST_NAME            FIRST_NAME           HOURS_PER_WEEK
---------- -------------------- -------------------- --------------
         1 Doe                  John                             40

To create the tables that you mentioned, you just place "CREATE TABLE..." code in front of each of the final SELECTs:

CREATE TABLE invalid_rec AS
select * from raw_rec y
where exists (select 'x'
                from (select emp_id, last_name from raw_rec
                      minus
                      select emp_id, last_name from emp_rec) x
               where x.emp_id = y.emp_id)
/

CREATE TABLE valid_rec AS
select * from raw_rec y
 where exists (select 'x'
                from (select emp_id, last_name from raw_rec
                      intersect
                      select emp_id, last_name from emp_rec) x
               where x.emp_id = y.emp_id)
/
*******************************************************************

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[deepgrewal22]

Mufasa,

You have saved me again. I will begin testing the code you have supplied. I just want to thank you for the extra effort you put into your response by actually simulating the tables and making this task easier than it was for me. My background is in Visual Basic, so SQL and PL/SQL are new to me. You have my vote.



Deep Grewal
"Microsoft Works" - oxymoron