Linking records together

#11

Thanks for the suggestions, I will definitely look into it.

"Malcolm Dew-Jones" <yf110 (AT) vtn1 (DOT) victoria.tc.ca> wrote

Quote:

PJ (pjv_dev.nospam (AT) geenspam (DOT) hotmail.com) wrote:
: Hi all,

: We need to track work items across several existing applications. Items
can
: start as a document on disk, can be renamed or moved, converted to a
task
: in an application, until they reach a certain state. The applications
know
: nothing about preceding or following applications and there is no common
: identifier for the items, which has always been a good thing.

: My idea is to create a table for all events. Each application will
record
: their
: own events. A minimum is one event per application with a starting and
: finishing situation for that application. Example: "file \\folder1\x.doc
was
: moved to \\folder2\y.doc". Another application may follow up with
: "\\folder2\y.doc was archived with id 12345". Each event will have a
date
: and time.

Use connect by to join the entries and sys_connect_by_path to show each
complete path.

You will get extra entries such as
e.g.

A->B
A->B->D
A->B->D->X

Filter that list so you only see the results where the end (e.g. X) does
not have a next connection.

CONNECT_BY_ISLEAF might help do that.

CONNECT_BY_ROOT might be useful when displaying the end points.

sys_connect_by_path might not even be required here.

#12

Thanks for your suggestions, I will look into it. Considering the amount of
records I will have in the event table, I will probably use a second table
with only the resulting report, and recalculate and update the appropriate
record when an event is inserted.

PJ

"Shakespeare" <whatsin (AT) xs4all (DOT) nl> wrote

Quote:

I just could not leave it alone:

CREATE OR REPLACE FUNCTION f_endpoint(p_startpoint IN NUMBER) RETURN
NUMBER IS
CURSOR c_nextpoint(b_startpoint NUMBER) IS
SELECT t2.event_id
FROM event_track t1, event_track t2
WHERE t1.event_id = b_startpoint
AND t1.end_item = t2.start_item;
r_nextpoint c_nextpoint%ROWTYPE;
BEGIN
OPEN c_nextpoint(p_startpoint);
FETCH c_nextpoint
INTO r_nextpoint;
IF c_nextpoint%NOTFOUND THEN
CLOSE c_nextpoint;
RETURN p_startpoint;
ELSE
CLOSE c_nextpoint;
RETURN f_endpoint(r_nextpoint.event_id);
END IF;
-- this could be done by a connect by and taking the last record
-- as well, but I love recursion ....
END;

SELECT t3.event_id, t3.start_item, t4.end_item
FROM (SELECT t.event_id, t.start_item, f_endpoint(t.event_id) end_event
FROM event_track t
WHERE NOT EXISTS
(SELECT 1 FROM event_track t2 WHERE t2.end_item = t.start_item))
t3,
event_track t4
WHERE t4.event_id = t3.end_event
ORDER BY 1;

EVENT_ID START_ITEM END_ITEM
1 A E
2 G K

Not the most optimal version I'm sure, but it works. Tested with more than
4 steps as well.

Shakespeare

#13

"PJ" <pjv_dev.nospam (AT) geenspam (DOT) hotmail.com> a écrit dans le message de news: 4aba2123$0$23459$703f8584 (AT) news (DOT) kpn.nl...

Quote:

Hi all,

We need to track work items across several existing applications. Items can
start as a document on disk, can be renamed or moved, converted to a task
in an application, until they reach a certain state. The applications know
nothing about preceding or following applications and there is no common
identifier for the items, which has always been a good thing.

My idea is to create a table for all events. Each application will record
their
own events. A minimum is one event per application with a starting and
finishing situation for that application. Example: "file \\folder1\x.doc was
moved to \\folder2\y.doc". Another application may follow up with
"\\folder2\y.doc was archived with id 12345". Each event will have a date
and time.

My table will probably have the following columns:

event_id number(10)
start_item varchar2(255)
end_item varchar2(255)
event_date date

Sample data:

1 A B 2009-10-11
2 G H 2009-10-12
3 B C 2009-10-13
4 H I 2009-10-17

Records can be linked from end_item to start_item (the next application in
line will continue where the previous application stops). So in this sample
data I have two sets of records (1 with 3, 2 with 4) and recognize two
starting points A and G with two end points C and I. From A to C takes
two days, from G to I takes 5 days.

Data entry is no problem. Reporting is. My resultset (a view, another table)
should be this:

A C 2
G I 5

How can I achieve this? I have looked at the connect by, but I get to many
records. So basically I have no idea where to start.

Thanks in advance,
PJ

create table event_track (
event_id number(10),
start_item varchar2(10),
end_item varchar2(10),
event_date date
)
/
insert all
into event_track values(1,'A','B',to_date('2009-10-11', 'RRRR-MM-DD'))
into event_track values(2,'G','H',to_date('2009-10-12', 'RRRR-MM-DD'))
into event_track values(3,'B','C',to_date('2009-10-13', 'RRRR-MM-DD'))
into event_track values(4,'H','I',to_date('2009-10-17', 'RRRR-MM-DD'))
into event_track values(5,'C','D',to_date('2009-10-19', 'RRRR-MM-DD'))
into event_track values(6,'I','J',to_date('2009-10-22', 'RRRR-MM-DD'))
into event_track values(7,'J','K',to_date('2009-10-23', 'RRRR-MM-DD'))
into event_track values(8,'D','E',to_date('2009-10-27', 'RRRR-MM-DD'))
into event_track values(9,'E','F',to_date('2009-11-27', 'RRRR-MM-DD'))
select * from dual;
alter session set nls_date_format='YYYY-MM-DD';

SQL> select * from event_track order by 1;
EVENT_ID START_ITEM END_ITEM EVENT_DATE
---------- ---------- ---------- ----------
1 A B 2009-10-11
2 G H 2009-10-12
3 B C 2009-10-13
4 H I 2009-10-17
5 C D 2009-10-19
6 I J 2009-10-22
7 J K 2009-10-23
8 D E 2009-10-27
9 E F 2009-11-27

9 rows selected.

SQL> select connect_by_root start_item start_item,
2 end_item,
3 event_date - connect_by_root event_date evt_days
4 from event_track
5 where connect_by_isleaf = 1
6 connect by prior end_item = start_item
7 start with start_item not in (select end_item from event_track)
8 order by 1
9 /
START_ITEM END_ITEM EVT_DAYS
---------- ---------- ----------
A F 47
G K 11

2 rows selected.

Regards
Michel

#14

PJ schreef:

Quote:

Thanks for your suggestions, I will look into it. Considering the amount of
records I will have in the event table, I will probably use a second table
with only the resulting report, and recalculate and update the appropriate
record when an event is inserted.

PJ

"Shakespeare" <whatsin (AT) xs4all (DOT) nl> wrote in message
news:4aba973d$0$83238$e4fe514c (AT) news (DOT) xs4all.nl...

I just could not leave it alone:

CREATE OR REPLACE FUNCTION f_endpoint(p_startpoint IN NUMBER) RETURN
NUMBER IS
CURSOR c_nextpoint(b_startpoint NUMBER) IS
SELECT t2.event_id
FROM event_track t1, event_track t2
WHERE t1.event_id = b_startpoint
AND t1.end_item = t2.start_item;
r_nextpoint c_nextpoint%ROWTYPE;
BEGIN
OPEN c_nextpoint(p_startpoint);
FETCH c_nextpoint
INTO r_nextpoint;
IF c_nextpoint%NOTFOUND THEN
CLOSE c_nextpoint;
RETURN p_startpoint;
ELSE
CLOSE c_nextpoint;
RETURN f_endpoint(r_nextpoint.event_id);
END IF;
-- this could be done by a connect by and taking the last record
-- as well, but I love recursion ....
END;

SELECT t3.event_id, t3.start_item, t4.end_item
FROM (SELECT t.event_id, t.start_item, f_endpoint(t.event_id) end_event
FROM event_track t
WHERE NOT EXISTS
(SELECT 1 FROM event_track t2 WHERE t2.end_item = t.start_item))
t3,
event_track t4
WHERE t4.event_id = t3.end_event
ORDER BY 1;

EVENT_ID START_ITEM END_ITEM
1 A E
2 G K

Not the most optimal version I'm sure, but it works. Tested with more than
4 steps as well.

Shakespeare

That sounds like a good idea to me.

Shakespeare

#15

Michel Cadot schreef:

Quote:

"PJ" <pjv_dev.nospam (AT) geenspam (DOT) hotmail.com> a écrit dans le message de news: 4aba2123$0$23459$703f8584 (AT) news (DOT) kpn.nl...
| Hi all,
|
| We need to track work items across several existing applications. Items can
| start as a document on disk, can be renamed or moved, converted to a task
| in an application, until they reach a certain state. The applications know
| nothing about preceding or following applications and there is no common
| identifier for the items, which has always been a good thing.
|
| My idea is to create a table for all events. Each application will record
| their
| own events. A minimum is one event per application with a starting and
| finishing situation for that application. Example: "file \\folder1\x.doc was
| moved to \\folder2\y.doc". Another application may follow up with
| "\\folder2\y.doc was archived with id 12345". Each event will have a date
| and time.
|
| My table will probably have the following columns:
|
| event_id number(10)
| start_item varchar2(255)
| end_item varchar2(255)
| event_date date
|
| Sample data:
|
| 1 A B 2009-10-11
| 2 G H 2009-10-12
| 3 B C 2009-10-13
| 4 H I 2009-10-17
|
| Records can be linked from end_item to start_item (the next application in
| line will continue where the previous application stops). So in this sample
| data I have two sets of records (1 with 3, 2 with 4) and recognize two
| starting points A and G with two end points C and I. From A to C takes
| two days, from G to I takes 5 days.
|
| Data entry is no problem. Reporting is. My resultset (a view, another table)
| should be this:
|
| A C 2
| G I 5
|
| How can I achieve this? I have looked at the connect by, but I get to many
| records. So basically I have no idea where to start.
|
| Thanks in advance,
| PJ
|
|

create table event_track (
event_id number(10),
start_item varchar2(10),
end_item varchar2(10),
event_date date
)
/
insert all
into event_track values(1,'A','B',to_date('2009-10-11', 'RRRR-MM-DD'))
into event_track values(2,'G','H',to_date('2009-10-12', 'RRRR-MM-DD'))
into event_track values(3,'B','C',to_date('2009-10-13', 'RRRR-MM-DD'))
into event_track values(4,'H','I',to_date('2009-10-17', 'RRRR-MM-DD'))
into event_track values(5,'C','D',to_date('2009-10-19', 'RRRR-MM-DD'))
into event_track values(6,'I','J',to_date('2009-10-22', 'RRRR-MM-DD'))
into event_track values(7,'J','K',to_date('2009-10-23', 'RRRR-MM-DD'))
into event_track values(8,'D','E',to_date('2009-10-27', 'RRRR-MM-DD'))
into event_track values(9,'E','F',to_date('2009-11-27', 'RRRR-MM-DD'))
select * from dual;
alter session set nls_date_format='YYYY-MM-DD';

SQL> select * from event_track order by 1;
EVENT_ID START_ITEM END_ITEM EVENT_DATE
---------- ---------- ---------- ----------
1 A B 2009-10-11
2 G H 2009-10-12
3 B C 2009-10-13
4 H I 2009-10-17
5 C D 2009-10-19
6 I J 2009-10-22
7 J K 2009-10-23
8 D E 2009-10-27
9 E F 2009-11-27

9 rows selected.

SQL> select connect_by_root start_item start_item,
2 end_item,
3 event_date - connect_by_root event_date evt_days
4 from event_track
5 where connect_by_isleaf = 1
6 connect by prior end_item = start_item
7 start with start_item not in (select end_item from event_track)
8 order by 1
9 /
START_ITEM END_ITEM EVT_DAYS
---------- ---------- ----------
A F 47
G K 11

2 rows selected.

Regards
Michel

In my first post, I originally mentioned you as one of the solutions,
but decided not to. Somehow I knew you would come up with a good and
simple answer.....

Compliments!

Shakespeare

#16

Very nice! Very clean coding and just what I need. Thanks!

"Michel Cadot" <micadot{at}altern{dot}org> wrote

Quote:

"PJ" <pjv_dev.nospam (AT) geenspam (DOT) hotmail.com> a écrit dans le message de
news: 4aba2123$0$23459$703f8584 (AT) news (DOT) kpn.nl...
| Hi all,
|
| We need to track work items across several existing applications. Items
can
| start as a document on disk, can be renamed or moved, converted to a
task
| in an application, until they reach a certain state. The applications
know
| nothing about preceding or following applications and there is no common
| identifier for the items, which has always been a good thing.
|
| My idea is to create a table for all events. Each application will
record
| their
| own events. A minimum is one event per application with a starting and
| finishing situation for that application. Example: "file \\folder1\x.doc
was
| moved to \\folder2\y.doc". Another application may follow up with
| "\\folder2\y.doc was archived with id 12345". Each event will have a
date
| and time.
|
| My table will probably have the following columns:
|
| event_id number(10)
| start_item varchar2(255)
| end_item varchar2(255)
| event_date date
|
| Sample data:
|
| 1 A B 2009-10-11
| 2 G H 2009-10-12
| 3 B C 2009-10-13
| 4 H I 2009-10-17
|
| Records can be linked from end_item to start_item (the next application
in
| line will continue where the previous application stops). So in this
sample
| data I have two sets of records (1 with 3, 2 with 4) and recognize two
| starting points A and G with two end points C and I. From A to C takes
| two days, from G to I takes 5 days.
|
| Data entry is no problem. Reporting is. My resultset (a view, another
table)
| should be this:
|
| A C 2
| G I 5
|
| How can I achieve this? I have looked at the connect by, but I get to
many
| records. So basically I have no idea where to start.
|
| Thanks in advance,
| PJ
|
|

create table event_track (
event_id number(10),
start_item varchar2(10),
end_item varchar2(10),
event_date date
)
/
insert all
into event_track values(1,'A','B',to_date('2009-10-11', 'RRRR-MM-DD'))
into event_track values(2,'G','H',to_date('2009-10-12', 'RRRR-MM-DD'))
into event_track values(3,'B','C',to_date('2009-10-13', 'RRRR-MM-DD'))
into event_track values(4,'H','I',to_date('2009-10-17', 'RRRR-MM-DD'))
into event_track values(5,'C','D',to_date('2009-10-19', 'RRRR-MM-DD'))
into event_track values(6,'I','J',to_date('2009-10-22', 'RRRR-MM-DD'))
into event_track values(7,'J','K',to_date('2009-10-23', 'RRRR-MM-DD'))
into event_track values(8,'D','E',to_date('2009-10-27', 'RRRR-MM-DD'))
into event_track values(9,'E','F',to_date('2009-11-27', 'RRRR-MM-DD'))
select * from dual;
alter session set nls_date_format='YYYY-MM-DD';

SQL> select * from event_track order by 1;
EVENT_ID START_ITEM END_ITEM EVENT_DATE
---------- ---------- ---------- ----------
1 A B 2009-10-11
2 G H 2009-10-12
3 B C 2009-10-13
4 H I 2009-10-17
5 C D 2009-10-19
6 I J 2009-10-22
7 J K 2009-10-23
8 D E 2009-10-27
9 E F 2009-11-27

9 rows selected.

SQL> select connect_by_root start_item start_item,
2 end_item,
3 event_date - connect_by_root event_date evt_days
4 from event_track
5 where connect_by_isleaf = 1
6 connect by prior end_item = start_item
7 start with start_item not in (select end_item from event_track)
8 order by 1
9 /
START_ITEM END_ITEM EVT_DAYS
---------- ---------- ----------
A F 47
G K 11

2 rows selected.

Regards
Michel

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Linking records together

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Linking records together - 09-24-2009 , 01:46 AM

Re: Linking records together - 09-24-2009 , 01:50 AM

Re: Linking records together - 09-24-2009 , 11:17 AM

Re: Linking records together - 09-24-2009 , 03:49 PM

Re: Linking records together - 09-24-2009 , 03:53 PM

Re: Linking records together - 09-25-2009 , 01:56 AM