Re: hierarchical query - returning a treewalked subset

On Nov 8, 8:05 am, Brian Tkatch <N/A> wrote:
> >ive been trying w/ something like this, but it doesnt work:
>
> Why does it not work? What error have you been getting?

it returns duplicate rows. the reason being (as far as i can tell), is that it first returns every product row that matches the name-criteria ("test*") as a LEVEL 1; then, it digs up all children for each returned row and builds levels under them. this can provide for misleading duplicates because a product may be a LEVEL 3 child, yet is now also returned as a LEVEL 1 row, which it is truly not.

currently im having to re-loop the returned resultset, and remove any LEVEL 1 row that is also defined as a LEVEL 2+.

make sense?

sm

> > 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.
Received on Thu Nov 08 2007 - 13:55:34 CST