Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: connect by: last row duplicated

Re: connect by: last row duplicated

From: <frank.van.bortel_at_gmail.com>
Date: 3 Nov 2006 01:06:44 -0800
Message-ID: <1162544804.572245.262170@h48g2000cwc.googlegroups.com>

News schreef:

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

Why would it be? You did request ID, didn't you? And ID 2 simply does not have a parent
Where's your 'START WITH' part of the statement?

SQL> l
  1 select ID, LPAD (' ', 2*level-2) || name as name, level   2 from test
  3 CONNECT BY prior ID = IDPARENT
  4* start with id = 1
SQL> /

        ID NAME                      LEVEL
---------- -------------------- ----------
         1 A                             1
         2   AA                          2

SQL> Received on Fri Nov 03 2006 - 03:06:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US