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

Joe,

in order to do that, you need to be able to identifiy which row of table a correspondents to which row of table b, ideally by a primary key which is not changed. In your example data, neither SS_NUMBER nor POSITION_NUMBER serve this purpose because both changed.

Rene

>
> 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

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html