Comparing dates between 2 tables

[biotech_pmp]

Hello,

I have a 2 tables.
table 1 - 1 record/row per ID.
table 2 - 2 records/row per ID.

This is a many(table 2) to one relationship (table 1).

I want to compare both dates from table 2 with table 1. Table 2 dates MUST be after table 1 dates.

execute immediate
begin
select sig_dt
into v_sig_dt
from table2
where id=:1 and sig_dt is not null;
using table1.ID;
when no_data_found then null;
end;

if table1.date1 > table2.sig_dt then
....output msg...
end if


of course, this is not how I written this code but almost close. The point is that it is only pulling 1 date instead of 2 dates. to compar with date1 date.

What would be another approach? LOOP? cursor? or left/right join?

Thanks for your help.

[skhanal]

You should be able to do it in a SELECT statement without a Cursor. Join on common key and compare table1 date with two dates in table2 in two clauses in WHERE clause.

[biotech_pmp]

You should be able to do it in a SELECT statement without a Cursor. Join on common key and compare table1 date with two dates in table2 in two clauses in WHERE clause.

Thank you for your suggestion.

The issue is that Oracle raises too many records fetch but I do not to retrieve 2 or more rows and not just one. What could I be doing wrong?

For example:
Table 1:
ID =999, 890,345
initials=wie, a-r, wed
dob=10MAY2000, 23JUL1992, 12DEC1970

Table 2:
ID=999,890,345
SIGdate=10OCT2008, 25OCT2008, 21APR2008
11OCT1999, 26OCT2008, 22APRI2008


As you can see, I hav 2 dates on table 2 from ID=999. I need to retrieve both dates and then compare with DOB.

DOB must be < before the SIG DATE. If it greater than SIGdate , then
DBMS_Output.Put_Line('Error: SIGdate is greater than DOB date');
.
.
.


Thanks for your help.