Re: Help writing SQL query

From: Barry Bulsara <bbulsara23_at_hotmail.com>
Date: Tue, 7 Dec 2004 19:32:05 -0000
Message-Id: <1102447852.5178.0@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 - 13:32:05 CST