Wrong Data In View

[fox12]

I created a db link successfully. Then I used a "Select count(*) from test_table@db_link" to query how many records. It returned "400". Then I queried the table "test_table" at the local database using "select count(*) from test_table". It returned "385". So far, both are correct. However, I want to find all records in "test_table@db_link", which does not exist in the "test_table" in the local db. I created the following view:
CREATE OR REPLACE VIEW record_diff
AS
SELECT a.ID, a.name
FROM test_table@db_link a, test_table b
WHERE a.ID <> b.ID

Please note that the two table structures are exactly the same, and both of them contain many of exactly same records.

Unfortunately, this view does not return the correct data. Instead, it returns a cartesian product. What may be wrong with this view?

Thanks.

Fox12

 

[carp]

Let's look at what you get with
WHERE a.ID <> b.ID

Suppose each table has three rows each, and they are all the same:


Table A.ID Table B.ID
1 1
2 2
3 3

Now look at what happens when a.ID = 1 - there are two rows in Table B where a.id <> b.id: 2 and 3. You will see the same behavior for every row in table A.

Try changing your view to
CREATE OR REPLACE VIEW record_diff
AS
SELECT a.ID, a.name
FROM test_table@db_link
WHERE a.ID IN (SELECT id FROM test_table@db_link
MINUS
SELECT id from test_table);
This will show all records in the remote table that do not exist in the local table.

 

[sem]

Carp, I think that IN clause is a bit redundant:

CREATE OR REPLACE VIEW record_diff
AS
SELECT ID, NAME
FROM test_table@db_link
minus
SELECT ID, NAME
FROM test_table

Regards, Dima

http://seminihin.narod.ru

 

[fox12]

Got it. See what is my problem.
Thanks.

 

[carp]

Dima -
I looked at that as well at the time of the post. However, it is only redundant IF you can guarantee that two rows with the same ID will not have different names. From the original problem statement, that was not guaranteed.