| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Need some SQL help Please...
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
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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steven Haas INET: steven.haas_at_snet.net 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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Tue Jan 21 2003 - 09:39:29 CST
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |