Re: connect by: last row duplicated

News schrieb:
> CREATE TABLE test
> (
> ID NUMBER NOT NULL,
> name VARCHAR2(10) NULL,
> IDPARENT NUMBER NULL
> )
> /
>
> insert into test (ID, name, IDPARENT) values (1, 'A', null);
> insert into test (ID, name, IDPARENT) values (2, 'AA', 1);
> commit;
>
>
> column name format A10
>
> select ID, LPAD (' ', 2*level-2) || name as name, level
> from test
> CONNECT BY prior ID = IDPARENT
> /
>
> ID NAME LEVEL
> ---------- ---------- ----------
> 2 AA 1
> 1 A 1
> 2 AA 2
>
>
> All results OK except the last row which is always duplicated with
> different level.
> Is this another 10g bug ??
>

You get what you told Oracle to make for you. Without specifying entry point in your hierarchy, database yield all possible hierarchies which satisfy your condition.

See in documentation what a START WITH is good for in CONNECT BY query.

Best regards

Maxim Received on Fri Nov 03 2006 - 03:18:30 CST