How can I recursively retrieve ALL of the parent records of an object?
If it is oracle then if you have a 5thlevel child like as in
2000
12345
45678
23456
12347
if we write this (look for oracle examples involving connect by for correct syntax)
select * from dtree where prior dataid=parentid start with dataid=2000
it will travesre down the line
select * from dtree where prior parentid=dataid start with dataid=12347
it will go the other way
Better query can be done using dtreeancestors if you have that working corrcetly.
My syntax was wrong a working example
To get a taxonomy replica starting from some container other than the enterprise root (dataid = 2000 or 2001) use the following Oracle code in a LiveReport with a Container input field. It also reports Compound documents and taxonomy within (sub)projects but does not sort elements within a container: select lpad(' ', 4 * (level - 1) + 1,'|...') || dtree.name "name", DTree.DataID, DTree.SubType, DTree.GIF, DTree.PermID, DTree.Reserved, DTree.ReservedBy from dtree where subtype in (0,136,202) start with dataid = %1 connect by prior dataid = abs(parentid) select dataid, lpad(' ',2*(level-1)) || name name, subtype from dtree where subtype in (0,1,144) start with dataid = 2000 connect by prior dataid = parentid
It is a SQL Server 2008 DB
Then in this forum there are sqlserver equivalents for oracle's connect by.Also newer sql servers do CTE's
so here is a general link
http://consultingblogs.emc.com/christianwade/archive/2004/11/09/234.aspx
Here is the old method of creating a function
https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=3371354&objAction=viewincontainer
a new hierarchy table called dtreeancestors is now in livelink which is basically a table recommender makes and builds it has the relation as well.There are examples of how to use it in this forum
Hi :
This is how i will write my query as appu suggested using dtreeancestors table.
And yes the query is in working condition for me for a SQL 2008 based db.
Select DATAID, OWNERID,NAME, CREATEDATE, ACT from Dtree where dataid in (select dataid from DTreeAncestors where DTreeAncestors.AncestorID= %1 and subtype = 0)
Provide Input type as "Container" and select param%1 as user inout1.
Same thing you could achieve with CTE (SQL function) , start writing your query like
SELECT p.ParentID ,p.DataID ,p.Name ,0 AS Level FROM DTree p WHERE p.DataID = 12345
and then go on constructing your CTE. Google more to fin on CTE.
