Re: Help writing SQL query

From: GQ <dbaguy_ott_at_yahoo.com>
Date: 7 Dec 2004 09:47:01 -0800
Message-ID: <1102441621.286884.150760@z14g2000cwz.googlegroups.com>

This is as far as I got, until you had a circular reference, which created an endless loop
in the recursive code ... (Brian -> Mary -> Peter ->Brian...)

create or replace function get_assoc(p_val in varchar2, p_ref in varchar2) return varchar2 is
cursor c_val is
select distinct decode(p_val,familymember1, familymember2,familymember1)
from family
where familymember1 = p_val

or familymember2 = p_val;
v_rel_name  varchar2(30);
v_ref_name  varchar2(30);

v_relatives varchar2(2000) := ' ';
begin
v_ref_name := p_ref;
open c_val;
fetch c_val into v_rel_name;
while c_val%found loop
if v_rel_name != p_ref then
if length(v_relatives) < 2 then
v_relatives := p_val ||', '|| v_rel_name; -- || get_assoc(v_rel_name,v_ref_name);
else
v_relatives := v_relatives ||', '|| v_rel_name; -- || get_assoc(v_rel_name,v_ref_name);
end if;
end if;
fetch c_val into v_rel_name;
end loop;
return v_relatives;

exception
when no_data_found then return v_relatives; end;
/
select get_assoc('Brian','Nobody else') from dual; ...produces
Brian, Claudine, Mary, Peter Received on Tue Dec 07 2004 - 11:47:01 CST