Re: Comparing data between 2 tables...

Rene,

Thanks a lot, I like that one. When I get select rights to their table, I will try your way. As a test, I tried it on 2 small tables. I'll find a way for it to display MINE, then THEIRS directly underneath it. As it is below, row3 should follow row1. I'll work it out...but THANKS again !!

      select
             'theirs: ' whos, t.ss_number, t.position_number from
               (select ss_number,position_number
                   from zztbpsb_assignmentb
                     minus
                select ss_number,position_number
                   from zztbpsb_assignment) t
           union
      select
             'mine: ', u.ss_number,u.position_number from
               (select ss_number,position_number
                   from zztbpsb_assignment
                     minus
                select ss_number,position_number
                   from zztbpsb_assignmentb) u

WHOS     SS_NUMBER       POSITION_NUMBER
-------- --------------- ---------------
mine:    012-40-2918     041487
mine:    017-40-5133     010219
theirs:  012-40-2911     041487
theirs:  017-40-5133     010218

Thanks again,
Joe

Rene Nyffenegger <rene.nyffenegger_at_gmx.ch> wrote in message news:<b42s5p$1qjku2$1_at_ID-82536.news.dfncis.de>...
> > Hello,
> >
> > Can someone please give me some tips on a simple way to compare all
> > columns and data on 2 tables in separate schemas in the same instance?
> >
> > I want to create a comparison report of, for example, MY.TABLE_SAL
> > -vs- THEIR.TABLE_SAL. I want to make sure that when I load Joe
> > Millionaires annual salary of $19,000 in MY.TABLE_SAL via sqlldr, they
> > also take the full $19000 and not just $1900 when they extracted the
> > data to be loaded into production.
> >
> > P.S. I'm not a DBA, so if some kind of DBA privies are needed for some
> > kind of tool or Data Dictionary table, please let me know.
> >
> > Any tips provided is appreciated.
>
>
> Something like
>
>
> select
> 'theirs: ' whose, t.* from
> (select * from their.table_sal minus select * from my.table_sal) t
> union
> select
> 'mine: ', u.* from
> (select * from my.table_sal minus select * from their.tablep) u;
>
> hth
>
> Rene Nyffenegger
Received on Tue Mar 04 2003 - 16:37:32 CST