Re: Linking records together

From: PJ <pjv_dev.nospam_at_geenspam.hotmail.com>
Date: Thu, 24 Sep 2009 08:50:07 +0200
Message-ID: <4abb169b$0$10688$703f8584_at_news.kpn.nl>

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.nl> wrote in message news:4aba973d$0$83238$e4fe514c_at_news.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
Received on Thu Sep 24 2009 - 01:50:07 CDT