Home » SQL & PL/SQL » SQL & PL/SQL » Query
Query [message #242920] Tue, 05 June 2007 09:13 Go to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Dear All,

I've 2 tables and same columns in that, i want to take the data which is not matching or not exist in any of the table. I've tried full outer join but it is not working. My table structure and possible data is mentioned downwards. Kindly help me out.

create table brnprog(
dirname varchar2(80),
gntname varchar2(40),
chksum number(20));

insert into brnprog values('/global/gnt','cioj8899.gnt',999999);
insert into brnprog values('/global/gnt','cpok7721.gnt',443321);
insert into brnprog values('/global/onl','nhyj662.gnt',5234422);
insert into brnprog values('/global/batch','dinq9822.gnt',736373);
insert into brnprog values('/global/batch','dpop2212.gnt',123456);



create table brnprog_tmp(
dirname varchar2(80),
gntname varchar2(40),
chksum number(20));

insert into brnprog_tmp values('/global/gnt','cioj8899.gnt',999999);
insert into brnprog_tmp values('/global/gnt','chjk7321.gnt',443321);
insert into brnprog_tmp values('/global/onl','nhyj662.gnt',5765422);
insert into brnprog_tmp values('/global/batch','dinq9822.gnt',755573);
insert into brnprog_tmp values('/global/batch','dpop2212.gnt',123456);

result expected

dirname gntname chksum_brnp chksum_brnptmp
________________________________________________________________
/global/gnt chjk7321.gnt missing 443321
/global/gnt cpok7721.gnt 443321 missing
/global/onl nhyj662.gnt 5234422 5765422
/global/batch dinq9822.gnt 736373 755573



Thanks in Advance
Amit
Re: Query [message #242922 is a reply to message #242920] Tue, 05 June 2007 09:15 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Sorry,

What i'm trying is mentioned below -

SELECT b.dirname DirName, b.gntname Gntname, NVL(a.chksum, 'Missing') BranchChecksum, NVL(b.chksum, 'Missing') HostChecksum FROM BRNPROG_TMP a, BRNPROG b WHERE a.gntname(+) = b.gntname AND a.dirname(+) = b.dirname AND a.chksum(+) != b.chksum


SELECT b.dirname DirName, b.gntname Gntname, NVL(a.chksum, 'Missing') BranchChecksum, NVL(b.chksum, 'Missing') HostChecksum FROM BRNPROG_TMP a FULL outer join BRNPROG b ON a.gntname = b.gntname AND a.dirname = b.dirname AND a.chksum != b.chksum


SELECT x.dirname hodir, y.dirname brndir, x.gntname hognt, y.gntname brngnt, x.chksum hochksum, y.chksum brnchksum FROM BRNPROG x, BRNPROG_TMP y WHERE
x.dirname = y.dirname AND x.gntname = y.gntname AND x.chksum != y.chksum


But I'm failed to get the result.
Re: Query [message #242927 is a reply to message #242920] Tue, 05 June 2007 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use "union all" and "minus".

Regards
Michel
Re: Query [message #242934 is a reply to message #242927] Tue, 05 June 2007 09:45 Go to previous messageGo to next message
amit.pandey
Messages: 64
Registered: August 2006
Location: Bangalore, India
Member
Dear Michel Cadot,

Thanks for your quick reply.

can you explain a little more or can you write a query for the same, for me.

Thanks & Regards
Amit pandey
Re: Query [message #242966 is a reply to message #242934] Tue, 05 June 2007 11:15 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read the second part of this article from Tom Kyte: Comparing the Contents of Two Tables

Regards
Michel
Previous Topic: START WITH CONNECT BY
Next Topic: CHECK THE STATUS IN FOR LOOP CURSOR
Goto Forum:
  


Current Time: Tue Dec 06 12:28:57 CST 2016

Total time taken to generate the page: 0.13564 seconds