I was able to write a query using UNION and MINUS to get correct results for
one generation only. I can re-create it and post it if it would help, but I
really think the only solution is procedural.
"Barry Bulsara" <bbulsara23_at_hotmail.com> wrote in message
news:1102447852.5178.0_at_lotis.uk.clara.net...
> I will try to work with your suggestion. Thank you for your pointers.
>
> Up to now my gut feeling is that I can do this with Oracle hierarchical
> queries. My SQL below is what I have been working on. It is bugged
('almost'
> gives me the required results) but I think I am almost there.
> Thank you
> Barry
>
> SQL> SELECT DISTINCT f1.familymember1 AS familymember
> 2 FROM family f1,family f2
> 3 CONNECT BY NOCYCLE PRIOR f1.familymember1=f2.familymember2
> 4 START WITH f1.familymember1='Brian'
> 5 UNION
> 6 SELECT DISTINCT f4.familymember1 AS familymember
> 7 FROM family f3,family f4
> 8 CONNECT BY NOCYCLE PRIOR f4.familymember1=f3.familymember2
> 9 START WITH f4.familymember1='Brian';
>
> FAMILYMEMBER
> --------------------
> Brian
> Claudine
> Dominic
> Elizabeth
> Fergie
> Francis
> Jean-Claude
> John Francois
> Mary
> Peter
>
> 10 rows selected.
>
> SQL>
> SQL> SELECT * FROM v$version;
>
> BANNER
> ----------------------------------------------------------------
> Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
> PL/SQL Release 10.1.0.2.0 - Production
> CORE 10.1.0.2.0 Production
> TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
> NLSRTL Version 10.1.0.2.0 - Production
>
> SQL>
>
>
>
>
>
> "GQ" <dbaguy_ott_at_yahoo.com> wrote in message
> news:1102441644.576437.298720_at_f14g2000cwb.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 - 14:58:09 CST
