Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    7

    Smile Unanswered: Comparison of oracle tables

    Hi All,

    The real problem that i'm facing is, i have to compare the output for around 300 tables (the tables are populated with same source data and it resides in different environment) from both the databases and each table has an average of around 50 to 60 columns. There are around 5 columns with date filed in all the tables. The date column name are the same across all the 300 tables. I need to omit the time value of the date column to omit the date column itself for the comparison since they don't match because of the different run-time.
    I can use the following query to do that, but how can i skip the date column or omit the time part of it???

    SELECT col1||','||col2||','||...||colx
    from
    ((SELECT <table_a> MINUS <table_b>) UNION
    (SELECT <table_b> MINUS <table_a>));

    Please suggest me how to do this??????? Any stored procedures??? Any other Methods????

    Any help on this is really appreciated.

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The "solution" will likely involve a Small Matter Of Programming.
    Keep in mind that when comparing "data" between two tables, it is essential that each table has the same columns as Primary Key.
    Without identical column PK's the task is impossible.
    "Differences" occur thre different ways.
    1) a PK can exist in table A & not in table B
    2) a PK can exist in table B & not in table A
    3) the PK's between table A & table B match, but the non-PK column(s) differ.
    Detecting all three cases can be achieved by making a SINGLE order pass thru both tables "concurrently".

    HTH & YMMV
    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.

  3. #3
    Join Date
    Nov 2004
    Posts
    7
    The tables structures in both the databases are exactly the same without any single difference...
    I really appreciate if i get any help on this

    Thanks

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    The thing is that every column of datatype date in Oracle must have a time portion, so you can't easily get rid of it ( that is, if its of date datatype ). What you can do is truncate the time portion ( with TRUNC ) of the date columns among all the tables so that all of them have the same time portion ( it's default to 12:00:00 AM ).

  5. #5
    Join Date
    Nov 2004
    Posts
    7
    Thanks for reply. Now, i'm using,

    SELECT col1||','||col2||','||...||colx
    from
    ((SELECT <table_a> MINUS <table_b> ) UNION
    (SELECT <table_b> MINUS <table_a> ));

    The above query will show the output of table a and table b.
    The problem is, If the difference is in millions,it will be difficult to spool the output.
    So I need to change the query in a way that if the difference is more than 500 records, it has to exit and the spool has to stop with a message saying "difference is more than 500 records"...

    Please let me know, how to implement the above functionality.....

    Thanks in Adv

  6. #6
    Join Date
    Nov 2004
    Posts
    7
    Now, i'm using,

    SELECT col1||','||col2||','||...||colx
    from
    ((SELECT <table_a> MINUS <table_b> ) UNION
    (SELECT <table_b> MINUS <table_a> ));

    The above query will show the output of table a and table b.
    The problem is, If the difference is in millions,it will be difficult to spool the output.
    So I need to change the query in a way that if the difference is more than 500 records, it has to exit and the spool has to stop with a message saying "difference is more than 500 records"...

    Please let me know, how to implement the above functionality.....

    Thanks in Adv

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Please let me know, how to implement the above functionality.
    Using PL/SQL, PERL, JAVA, etc.
    Alternatively, it is straightforward to identify those rows with exact matches.
    If you eliminate those rows from each of the original tables, then you are left with the rows that are different in each table. QED
    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
  •