We sometimes see discussions in tickets, other forums, and the like, that may be quite useful in the future. I thought it would be useful to post a collation of information around a GetNodePath function.
There was, in past, a function posted for Oracle, by a Scott Wallace, seen here: https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=4461418&objAction=viewincontainer&ShowReplyEntry=8848087#
His function:
CREATE FUNCTION GETLLPATH(objectid IN NUMBER) RETURN VARCHAR2 IS dir VARCHAR2(32767);
obj_id NUMBER;
CURSOR Name_Cursor IS SELECT A.NAME, A.PARENTID FROM DTREE A WHERE A.DATAID = obj_id;
name Name_Cursor%rowtype;
BEGIN dir := NULL; obj_id := objectid; LOOP OPEN Name_Cursor;
FETCH Name_Cursor INTO name;
EXIT WHEN Name_Cursor%NOTFOUND or name.NAME = 'Enterprise';
IF dir IS NOT NULL THEN dir := (name.NAME || ':' || dir) ; END IF;
IF dir IS NULL THEN dir := name.NAME; END IF;
obj_id := name.PARENTID;
CLOSE Name_Cursor; END LOOP;
return(dir); END;
I thought it would be worth it posting an MS SQL version, for anyone who would like to make use of it:
CREATE FUNCTION [GETLLPATH](@dataid bigint)
RETURNS varchar(max)
AS
BEGIN
declare @path varchar(max);
SET @path='';
WITH Child(ParentID,DataID,Name) AS
(SELECT Child.ParentID
,Child.DataID
,Child.Name
FROM DTreeCore Child
WHERE Child.DataID=@dataid
UNION ALL
SELECT Parent.ParentID
,Parent.DataID
,Parent.Name
FROM DTreeCore as Parent,Child
WHERE Child.ParentID = Parent.DataID
)SELECT @path=@path+name+':' from Child order by DataID
SET @path=SUBSTRING(@path,0,LEN(@path))
RETURN @path
END
Alternatively, you can forego a function, entirely, and do a SELECT * from DBrowseAncestors, which may also suit your purposes.
I hope that gives some of you some ideas!
