Good morning List, Please bare with me, this is somewhat long with the DDL and DML included.
I have two tables that are populated by triggers to be used to audit data changes. The source and target tables are identical in structure with the addition of the DML and sequence columns iin the target. For each key there will be at least an insert (I) row with possibly update (U) or delete (D) rows in chronological order. As each row is inserted it gets a sequence number. I am having some trouble getting the data to return in the correct order. It must be a chronological return of each parent rows and its child rows that fall chronologically between the insert/updates/deletes. What I expect is this (dates abbreviated) for par_key = 100: par_key par_col par_insert par_update par_dml par_seq chi_key chi_col chi_insert chi_update chi_dml chi_seq 100 100 1/1/2003 I 1 100 100 1/1/2003 I 1 100 200 1/2/2003 1/2/2003 U 4 100 100 1/1/2003 I 1 100 200 1/2/2003 1/3/2003 U 5 100 100 1/1/2003 I 1 100 200 1/2/2003 1/3/2003 U 5 100 200 1/1/2003 1/4/2003 U 4 100 200 1/2/2003 1/3/2003 U 5 100 300 1/1/2003 1/5/2003 U 5 100 200 1/2/2003 1/3/2003 U 5 100 400 1/1/2003 1/6/2003 U 6 I have used this as basis for starting, but can't seem to get it to show in the correct order or without extra rows. select p.par_key, p.par_col, p.par_insert, p.par_update, p.par_dml, p.par_seq, c.chi_key, c.chi_col, c.chi_insert, c.chi_update, c.chi_dml, c.chi_seq from tab_parent p, tab_child c where p.par_key = c.chi_key and c.chi_insert between p.par_insert and nvl(p.par_update,c.chi_insert) or c.chi_update between p.par_insert and nvl(p.par_update,c.chi_insert) order by par_key,par_col,par_seq,chi_key,chi_col,chi_seq; ----------------------- drop table tab_parent; create table tab_parent (par_key number, par_col number, par_insert date, par_update date, par_dml char(1), par_seq number); drop table tab_child; create table tab_child (chi_key number, chi_col number, chi_insert date, chi_update date, chi_dml char(1), chi_seq number); truncate table tab_parent; truncate table tab_child; insert into tab_parent values (100,100,to_date('10-jan-2003 08:00:00','dd-mon-yyyy hh24:mi:ss'),null,'I',1); insert into tab_parent values (200,200,to_date('10-jan-2003 08:00:00','dd-mon-yyyy hh24:mi:ss'),null,'I',2); insert into tab_parent values (300,300,to_date('12-jan-2003 20:00:00','dd-mon-yyyy hh24:mi:ss'),null,'I',3); insert into tab_parent values (100,200,to_date('10-jan-2003 10:00:00','dd-mon-yyyy hh24:mi:ss'), to_date('13-jan-2003 11:00:00','dd-mon-yyyy hh24:mi:ss'),'U',4); insert into tab_parent values (100,300,to_date('10-jan-2003 10:00:00','dd-mon-yyyy hh24:mi:ss'), to_date('15-jan-2003 12:00:00','dd-mon-yyyy hh24:mi:ss'),'U',5); insert into tab_parent values (200,200,to_date('10-jan-2003 10:10:00','dd-mon-yyyy hh24:mi:ss'), to_date('17-jan-2003 15:30:00','dd-mon-yyyy hh24:mi:ss'),'U',6); insert into tab_parent values (200,300,to_date('10-jan-2003 10:10:00','dd-mon-yyyy hh24:mi:ss'), to_date('17-jan-2003 15:30:01','dd-mon-yyyy hh24:mi:ss'),'U',7); insert into tab_parent values (200,400,to_date('10-jan-2003 10:10:00','dd-mon-yyyy hh24:mi:ss'), to_date('18-jan-2003 16:50:00','dd-mon-yyyy hh24:mi:ss'),'U',8); insert into tab_parent values (300,300,to_date('12-jan-2003 20:00:00','dd-mon-yyyy hh24:mi:ss'), to_date('19-jan-2003 07:30:00','dd-mon-yyyy hh24:mi:ss'),'D',9); insert into tab_child values (100,100,to_date('10-jan-2003 10:00:00','dd-mon-yyyy hh24:mi:ss'),null,'I',1); insert into tab_child values (200,200,to_date('10-jan-2003 10:10:00','dd-mon-yyyy hh24:mi:ss'),null,'I',2); insert into tab_child values (300,300,to_date('12-jan-2003 20:00:00','dd-mon-yyyy hh24:mi:ss'),null,'I',3); insert into tab_child values (100,200,to_date('10-jan-2003 10:00:00','dd-mon-yyyy hh24:mi:ss'), to_date('10-jan-2003 12:00:00','dd-mon-yyyy hh24:mi:ss'),'U',4); insert into tab_child values (100,300,to_date('10-jan-2003 10:00:00','dd-mon-yyyy hh24:mi:ss'), to_date('15-feb-2003 12:00:00','dd-mon-yyyy hh24:mi:ss'),'U',5); insert into tab_child values (100,400,to_date('10-jan-2003 10:00:00','dd-mon-yyyy hh24:mi:ss'), to_date('16-feb-2003 12:33:00','dd-mon-yyyy hh24:mi:ss'),'U',6); insert into tab_child values (200,300,to_date('10-jan-2003 10:10:00','dd-mon-yyyy hh24:mi:ss'), to_date('15-jan-2003 08:02:00','dd-mon-yyyy hh24:mi:ss'),'U',7); insert into tab_child values (200,400,to_date('10-jan-2003 10:10:00','dd-mon-yyyy hh24:mi:ss'), to_date('17-jan-2003 15:00:00','dd-mon-yyyy hh24:mi:ss'),'U',8); insert into tab_child values (200,500,to_date('10-jan-2003 10:10:00','dd-mon-yyyy hh24:mi:ss'), to_date('17-jan-2003 15:30:00','dd-mon-yyyy hh24:mi:ss'),'U',9); insert into tab_child values (200,500,to_date('10-jan-2003 10:10:00','dd-mon-yyyy hh24:mi:ss'), to_date('18-jan-2003 16:50:00','dd-mon-yyyy hh24:mi:ss'),'D',10); commit; ---------------------------- Thanks folks... Steve ===== Steve Haas Opus Consultants, LLC 860.408.1512 (office/fax) 860.651.9475 (home) [EMAIL PROTECTED] [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steven Haas INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).