SQL Query for Auditing a Folder with Subfolders and Documents without using Connect By Prior (SQL Da

Chad Latka

Looing for a SQL Query to build a LiveReport for Auditing (audit events) for a specific Content Server Folder with Subfolders and Documents located in them.

We do not have Oracle so cannot use Connect By Prior

Any help appreciated.

appuGmail

You either have to use a SQL Server Function available in this forum which has been put since 2004 or so.Look for "CTE" and drop that as a function.However if you have a good dependable DTreeAncestors table you can get the hierarchy thus

CTE examples https://stackoverflow.com/questions/31827817/sql-server-equivalent-of-oracle-connect-by-prior-and-order-siblings-by 

Written for Livelink schema  the two links was posted by me a while back you also get this function when you attend a LR Training class

https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=19704630&objAction=viewincontainer 

Some thing that shows all items in a Folder the Data Id in this query is the one that's showing up in DauditNew

select DT.[Name],DT.DataID,DT.SubType ,DT.ParentID, DTA.AncestorID from DTreeAncestors DTA,DTree DT where DTA.DataID = DT.DataID

and DTA.AncestorID=305105 order by DT.ParentID;

Similarly OT added a DBrowseAncestors which can be used as well.

  

..... appnair appunair( Livelink Rocks  )

Well, if I called the wrongnumber, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937 

On Fri, Jun 22, 2018 at 9:39 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

SQL Query for Auditing a Folder with Subfolders and Documents without using Connect By Prior (SQL Database not Oracle)

Posted bychad.latka@hii-ingalls.com (Latka, Chad)On 06/22/2018 10:35 AM

Looing for a SQL Query to build a LiveReport for Auditing (audit events) for a specific Content Server Folder with Subfolders and Documents located in them.We do not have Oracle so cannot use Connect By PriorAny help appreciated.

---

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	My Support