Hi All,

I have a problem with a table whereby everytime a record is created
another is created showing the opposite of this:

e.g.

Rel_Id (PK)|Source_Id|To_Id|Start_Date|End Date
R1 |P1 |P2 |2005-01-05|2005-02-05
R3 |P2 |P1 |2005-01-05|2005-02-05

The Rel_Id is a PK but is not nessarily created incrementally.

For some unknown reason in some instances only one of the rows have
been created either the first or the second.

What I need is to create a SQL script which finds those rows where the
corresponding opposite row has not been created.

I have tried, nested loops, joins everything I can think of, but maybe
I am looking this the wrong way.

The oracle environment I am using is (I am at a client site):
Oracle7 Server Release 7.3.3.0.0 - Production Release

Kind Regards,

Kal

kalgill@talk21.com wrote:
> Hi All,
>
> I have a problem with a table whereby everytime a record is created
> another is created showing the opposite of this:
>
> e.g.
>
> Rel_Id (PK)|Source_Id|To_Id|Start_Date|End Date
> R1 |P1 |P2 |2005-01-05|2005-02-05
> R3 |P2 |P1 |2005-01-05|2005-02-05
>
> The Rel_Id is a PK but is not nessarily created incrementally.
>
> For some unknown reason in some instances only one of the rows have
> been created either the first or the second.
>
> What I need is to create a SQL script which finds those rows where the
> corresponding opposite row has not been created.
>
> I have tried, nested loops, joins everything I can think of, but maybe
> I am looking this the wrong way.
>
> The oracle environment I am using is (I am at a client site):
> Oracle7 Server Release 7.3.3.0.0 - Production Release
>
> Kind Regards,
>
> Kal

Oracle 7.3.3? My memory isn't good enough to remember much about
it though I have an urge to sugget you patch to 7.3.4 (just kidding).

I am really uncomfortable with the idea that any solution is going to
remove invalid records and not accidentally delete valid records.
How do you define the difference? Your example does not make it clear.

But something like:

INSERT INTO some_table
(rid, col1a, col2a, col1b, col2b)
SELECT rowid, a.col1, a.col2, b.col1, b.col2
FROM mytable a, mytable b
WHERE a.col1 = b.col2
AND a.col2 = b.col1
AND a.start_date = b.start_date
and a.end_date = b.end_date;

or similar
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)