hierarchical query - returning a treewalked subset

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 Received on Wed Nov 07 2007 - 16:01:09 CST