Home -> Usenet -> comp.databases.oracle -> Weird behaviour with START WITH and CONNECT BY

Weird behaviour with START WITH and CONNECT BY

From: Bernard Drolet <le_pul_at_yahoo.ca>
Date: 5 Nov 2003 06:32:53 -0800
Message-ID: <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 - 15:32:53 CET












Original text of this message