Re: Help writing SQL query

I would be interested in seeing the query Alan, yes please do post.

I was initially loathe to use set operators like UNION but in the end couldn't see a way around not using them. Although I haven't seen your query yet, there does seem commonality in the way we are thinking. I keen too to see whether your query gives the same resultset if the query is modified to START WITH any member of a given family (ie get the same resultset if queried for Brian, Mary, Francis, Bruce etc). Thanks in advance
Barry

"Alan" <alan_at_erols.com> wrote in message news:31mjr2F3bp95oU1_at_individual.net...
>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 - 15:09:37 CST