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).
