Re: Selecting All Parents in a Path Enumerated Tree

"Ones Self" <nutgg001_at_sneakemail.com> a écrit dans le message de news:a2122d77.0410291401.6891b9f_at_posting.google.com...
> Hi,
>
> I have a tree structure which is maintained through the use of a path
> enumerated column:
>
> CREATE TABLE items (
> item_id NUMERIC NOT NULL,
> path VARCHAR2(64) NOT NULL
> );
>
> The path is is a colon separated list of ids of the nodes of the tree.
> So, for example, in this structure:
>
> 0 -> 1 -> 2 -> 3 -> 4
>
> item id 4 would have a path of '0:1:2:3' (0 is the root of all
> items, and does not actually exist). Notice that the path does not
> include the item's own id.
>
> I would like to select all of the items in a given item's path:
>
> SELECT id, path FROM items WHERE id IN (PATH_TO_IDS(path));
>
> or maybe:
>
> SELECT id, path FROM items WHERE PATH_EQUALS(id, path));
>
> or maybe something else altogether. This should return:
>
> ITEM_ID PATH
> ------- -------
> 1 0
> 2 0:1
> 3 0:1:2
> 4 0:1:2:3

Something like:

def baseId=4
select id, path
from items, (select path basePath from items where id=&baseId) where basePath like '%:'||id||':%' or basePath like id||':%' or basePath like '%:'||id union all /* if you also want the BaseId full path */ select id, path where id = &baseId
order by 2
/

-- 
Regards
Michel