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