Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: about hierarchical query

Re: about hierarchical query

From: ErpÓ <palo_at_email.it>
Date: Wed, 10 Nov 2004 16:14:11 +0100
Message-ID: <0fqkd.1950$n%2.1339@tornado.fastwebnet.it>


yes, but

select level, node
from mytable
start with parent_id is null
connect by prior id = parent_id
*returns all tree*

select level, node
from mytable
where mycondition
start with parent_id is null
connect by prior id = parent_id
*returns only leaves*:
level node
--------- ---------

3          C1
3          C5
3          C6

I've tried:
- where condition in "start with" statement is apply to roots - where condition in "connect by" statement is apply only to first level under roots

How can I proceed?
thanks
Paolo

IANAL_VISTA wrote:

> Erpa <Erpa_at_email.it> wrote in news:cmt01j$2h8$1_at_newsread.albacom.net:
> 
> 

>>hi all,
>>how can I write an hierarchical query to view all level of my tree, but
>>only the branches end with leaves that satisfy some conditions?
>>
>>
>>for example, this is my tree in my hierarchical table:
>>
>> A1 A2
>> / \ / \
>> / \ / \
>> B1 B2 B3 B4
>> / \ / \ / \ / \
>> C1 C2 C3 C4 C5 C6 C7 C8
>>
>>if only C1, C5 and C6 satisfy some conditions i wish to have this result
>>from my query:
>>
>> A1 A2
>> / /
>> / /
>> B1 B3
>> / / \
>> C1 C5 C6
>>
>>level node
>>--------- ---------
>>1 A1
>>2 B1
>>3 C1
>>1 A2
>>2 B3
>>3 C5
>>3 C6
>>
>>thanks for your help
>>Paolo
>>
> 
> 
> CONNECT BY clause on SELECT
Received on Wed Nov 10 2004 - 09:14:11 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US