Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Location
    London
    Posts
    3

    Unanswered: Comparing data between two or more tables

    Folks, I have been asked to prepare a report; comparing the data contents of two tables and flagging up where the data may not match ie. surname in table 1 doesn't match surname in table 2 or postcode in table 1 does not match the postcode in table 2

    I have written this UNION query; but it appears to be running for ever and never returns a result set - I should add that one of the tables is accessible via a database link.

    select * from (select to_number(c.customerid),
    d.new_forename,
    d.new_middle_names,
    d.new_surname,
    b.family_name_16,
    a.prefix_code,
    a.dob,
    a.gender_code
    from user.people_view@db1_db2 a,
    user.people_address@db1_db2 b,
    user.customers@db1_db2 c
    user.change_people_name@db1_db2 d
    minus
    select ID_Number,
    First_Name,
    Middle_Name,
    Surname,
    Birth_Cert_Surname,
    Title,
    Date_of_Birth,
    Gender
    from user.stg_customer
    )
    UNION ALL
    Select * from (select id_Number,
    First_Name,
    Middle_Name,
    Surname,
    Birth_Cert_Surname,
    Title,
    Date_of_Birth,
    Gender
    from user.stg_customer
    minus
    select to_number(c.customerid),
    d.new_forename,
    d.new_middle_names,
    d.new_surname,
    b.family_name_16,
    a.prefix_code,
    a.dob,
    a.gender_code
    from user.customer_view@db1_db2 a,
    user.customer_address@db1_db2 b,
    user.customers@db1_db2 c,
    user.change_customer_name@db1_db2 d
    )

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You could try and use the DBMS_COMPARISON PL/SQL package.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    why querying every table over SQL*Net?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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