Sorry, my fault. The code runs but never returns anything. After 15
minutes I killed it.

So basically I was looking for a reality check. This code basically
looks right?

Svein Erling Tysvaer wrote:

> Hi Tom!
>
> Is there a question somewhere, or did you just want to show us some code
> that should get some records from pd_list and the topmost parent for
> each of them?
>
> The one advice I would give you regarding your code, is to change from
> 'pd_origin = 4' to 'l.pd_origin = 4'.
>
> Set
>
> Tom Miller wrote:
>
>> Here is the code. PD_ID is the Key for the table and PARENT_PD is the
>> parent id for the PD_ID key.
>> If the record is a parent, then PARENT_PD = -1.
>>
>> Thanks for the help.
>>
>> create procedure get_parent
>> RETURNS (
>> p1 INTEGER,p2 INTEGER
>> )
>> AS
>>
>> DECLARE VARIABLE pdid INTEGER;
>> DECLARE VARIABLE pdid_new INTEGER;
>> DECLARE VARIABLE parentpd INTEGER;
>> BEGIN
>>
>> for select l.pd_id, l.parent_pd from pd_list l where
>> pd_origin = 4 and
>> not exists ( select p.pd_id from pd_duty_stmt p where l.pd_id=p.pd_id)
>> into :pdid, :parentpd do begin
>>
>> p1 = :pdid;
>> WHILE ( :parentpd != -1 ) DO BEGIN
>> select parent_pd, pd_id from pd_list where pd_id = :parentpd into
>> :parentpd, :pdid_new;
>> p2=:pdid_new;
>> end
>> SUSPEND;
>> end
>>
>>