Re: hierarchical query - returning a treewalked subset

On Thu, 08 Nov 2007 11:55:34 -0800, SpaceMarine <spacemarine_at_mailinator.com> wrote:

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

Perhaps the START WITH needs to be defined to choose only a level 1.

B.

>
>
>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 - 14:43:09 CST