Re: Linking records together

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Thu, 24 Sep 2009 22:49:04 +0200
Message-ID: <4abbdb45$0$83233$e4fe514c_at_news.xs4all.nl>

PJ schreef:

> 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

>
>

That sounds like a good idea to me.

Shakespeare Received on Thu Sep 24 2009 - 15:49:04 CDT