Re: Weird behaviour with START WITH and CONNECT BY

you have 15 records in your table where object_id of 19504 equals the parentobject_id...

What does parentobject_id of -1 mean in your system ?

Hey if you want unique try select distinct

"Bernard Drolet" <le_pul_at_yahoo.ca> wrote in message news:ee7856eb.0311050632.65b3aa5d_at_posting.google.com...
> Hi, I have a recursive table containing complex SGML documents.
> The table has a primary key "objectref"; to determine what object is
> actually valid, I have the columns validtime and obsoletetime, which
> tell me when the object becomes, as you expect, valid and obsolete.
>
> I ran the following query, that is supposed to return me the structure
> from a specific node (identified with objectref = 7754087) up to the
> top parent.
>
> SELECT
> objectref,
> objectname,
> objectid,
> parentobjectid,
> validtime,
> obsoletetime
> FROM
> Objectnew
> WHERE
> validtime <= SYSDATE AND
> obsoletetime > SYSDATE
> START WITH objectref = 7754087
> CONNECT BY objectid = PRIOR parentobjectid
> ORDER BY objectid DESC;
>
>
> Now, if you look at the result,
>
> OBJECTREF OBJECTNAME OBJECTID PARENTOBJECTID VALIDTIME OBSOLETET
> ---------- ---------- ---------- -------------- --------- ---------
> 7754087 CHAPTER 490772 19524 22-AUG-01 31-DEC-12
> 30990083 AMM 19524 19504 27-AUG-03 31-DEC-12
> 18300296 CRJ_AMM 19504 -1 11-APR-00 31-DEC-12
> 18300296 CRJ_AMM 19504 -1 11-APR-00 31-DEC-12
> 18300296 CRJ_AMM 19504 -1 11-APR-00 31-DEC-12
> 18300296 CRJ_AMM 19504 -1 11-APR-00 31-DEC-12
> 18300296 CRJ_AMM 19504 -1 11-APR-00 31-DEC-12
> 18300296 CRJ_AMM 19504 -1 11-APR-00 31-DEC-12
> 18300296 CRJ_AMM 19504 -1 11-APR-00 31-DEC-12
> 18300296 CRJ_AMM 19504 -1 11-APR-00 31-DEC-12
> 18300296 CRJ_AMM 19504 -1 11-APR-00 31-DEC-12
> 18300296 CRJ_AMM 19504 -1 11-APR-00 31-DEC-12
> 18300296 CRJ_AMM 19504 -1 11-APR-00 31-DEC-12
> 18300296 CRJ_AMM 19504 -1 11-APR-00 31-DEC-12
> 18300296 CRJ_AMM 19504 -1 11-APR-00 31-DEC-12
> 18300296 CRJ_AMM 19504 -1 11-APR-00 31-DEC-12
> 18300296 CRJ_AMM 19504 -1 11-APR-00 31-DEC-12
>
> 17 rows selected.
>
> Can somebody explain me why I have 15 times the last row repeated (it
> is the same, as identified by its primary key objectref), when I was
> expecting to get it only once ?)
>
>
> Bernard Drolet
Received on Wed Nov 05 2003 - 16:32:36 CET