Re: hierarchical query - returning a treewalked subset

"SpaceMarine" <spacemarine_at_mailinator.com> schreef in bericht news:1194472869.139869.54650_at_k79g2000hse.googlegroups.com...
> hello,
>
> (i had a previous post similar to this, but dont think it states the
> problem accurately).
>
> - i have a product table of 20,000 rows
> - they are hierarchical based on ObjectID & ParentID columns
> - its too many rows to show at once, so i need a query to return a
> subset (based on name wildcard)
> - for each row returned, i need to include its complete descendants
> tree
>
> for instance, if one searched for 'test%', it would return all product
> rows where the name begins w/ "test", but it would also include all
> rows that reference each row as a ParentID value.
>
> i think this must be possible, probably w/ a CONNECT BY PRIOR, but im
> having much difficulty with it.
>
> ive been trying w/ something like this, but it doesnt work:
>
> SELECT
> LEVEL,
> v.ObjID,
> v.Name,
> v.ParentID
>
> FROM
> v_myData v
>
> START WITH (UPPER(v.Name) LIKE UPPER('test%'))
> CONNECT BY PRIOR ObjID = ParentID
>
> ...can this be done in a simple single statement? or should i just get
> all the rows in one statement, and then get all distinct rows that
> refer to each row in another statement, and lump them into one
> resultset?
>
>
> thanks!
> sm
>

I don't think you can use a start with clause that returns more than one row... maybe you can use an outer query returning the start rows and an inner query returning the treewalk (or vice versa?).

Shakespeare Received on Thu Nov 08 2007 - 03:25:27 CST