Re: Limit the levels for a query with CONNECT BY

Hello Bernard,

If I understand correctly what you want to do, then it's really simple:

SELECT id, name

     FROM Mytable
     WHERE name = 'XYZ'
     STARTING WITH id = &1
     CONNECT BY id = PRIOR id AND PRIOR name <>  'XYZ';

Rgds.

"Bernard Drolet" <le_pul_at_yahoo.ca> wrote in message news:ee7856eb.0311050650.4cbb0e2a_at_posting.google.com...
> sybrandb_at_yahoo.com wrote in message
 news:<a1d154f4.0311050031.2f4d988c_at_posting.google.com>...
> >
> > Hint: there is a LEVEL pseudo function.
> >
> > Sybrand Bakker
> > Senior Oracle DBA
>
> I have two concerns with LEVEL:
>
> First,
>
> I do not know in advance at what level the specific row is ! The table
> contains the structure and content of complex SGML documents; the SGML
> structure (DTD) can be different from one document to the other and
> can even change over time
>
> (for example, I can have a document with
> PUBLICATION - CHAPTER - SECTION - TASK
> structure today, but in future, the person in charge of the
> publication can decide to add a layer, as in
> PUBLICATION - CHAPTER - SECTION - PAGEBLOCK - TASK,
> so the level to find the TASK is now changed and the query will not
> find it anymore).
>
> Second,
>
> Even if I ask to display only the, for example, 3 first LEVELs, am I
> right when I say the query will process the complete tree structure
> anyway before returning me only the rows I'm interested in ?
>
>
>
> Bernard Drolet
Received on Thu Nov 06 2003 - 23:10:08 CET