Re: hierarchical query - returning a treewalked subset

On Wed, 07 Nov 2007 14:01:09 -0800, SpaceMarine <spacemarine_at_mailinator.com> wrote:

>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:

Why does it not work? What error have you been getting?

>
> 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?

Yes.

B.

> 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
Received on Thu Nov 08 2007 - 08:05:24 CST