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
>
> end