Home » SQL & PL/SQL » SQL & PL/SQL » Joins (Oracle 10g)
Joins [message #446315] Sun, 07 March 2010 20:51 Go to next message
Santhosh05
Messages: 13
Registered: March 2010
Location: US
Junior Member
Hi,

I have two tables A with columns a.key, a.location_code, a.status and a.first_name and table B with cols b.key, b.location_code, b.status and b.first_name.

I want to find the missing records between the two tables and as well check whether each column is populated correctly. That is if u take a record with id 1 check if loc_code is same in both the tables and if they are different, insert the key and first record column and second record column into a new table. And similarly if there is no record wiht that particular id in the second table, insert the record.

For missing records in the sense for records which are present in A but not in B, am using

Select a.key_no, a.loc_code, b.loc_code
from A,B
where a.key_no=b.key_no(+)
and b.key_no IS NULL

But the problem is I need to put some constraints on the B table like b.status='Married'and b.loc_code='CA'. When am using this condition in the above query, it's throwing me error saying cannot use outer join operator in and or or.

And I could not figure out how to check for the columns being populated correctly between the two tables and at the same time check for missing ones.

Any help is appreciated. I need to implement this with joins(do not want to do it with MINUS).

Thanks,
Santhosh

Re: Joins [message #446317 is a reply to message #446315] Sun, 07 March 2010 21:02 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Joins [message #446319 is a reply to message #446315] Sun, 07 March 2010 21:13 Go to previous messageGo to next message
yurkoz
Messages: 26
Registered: September 2009
Location: bronx
Junior Member
select
from
A,
(
select key from
B
where < my constrain >

) Q
where A.key=Q.Key(+)
and Q.K is null



Re: Joins [message #446322 is a reply to message #446315] Sun, 07 March 2010 21:18 Go to previous messageGo to next message
Santhosh05
Messages: 13
Registered: March 2010
Location: US
Junior Member
Thanks for your reply.

I cannot use subquery too as I need to implement this in ETL tool(Oracle Data Integrator).

Do u have any other options.

Thanks,
Santhosh
Re: Joins [message #446323 is a reply to message #446315] Sun, 07 March 2010 21:25 Go to previous messageGo to next message
yurkoz
Messages: 26
Registered: September 2009
Location: bronx
Junior Member
did you try

Select a.key_no, a.loc_code, b.loc_code
from A,B
where a.key_no=b.key_no(+)
and b.key_no IS NULL
b.<field>(+) != 'KUKU'
Re: Joins [message #446325 is a reply to message #446315] Sun, 07 March 2010 21:43 Go to previous message
Santhosh05
Messages: 13
Registered: March 2010
Location: US
Junior Member
If I have to include tht then may be I need to include 30 codes in NOT in condition instead of sepecifying 2 codes in IN condition which is kind of tedious.

Thank you so much for your help though!!!
Previous Topic: Help Creating Single Table Cluster
Next Topic: Select Query Help
Goto Forum:
  


Current Time: Thu Dec 08 00:40:30 CST 2016

Total time taken to generate the page: 0.09916 seconds