If you want to get everything from the root folder down, youcould try using the ancestor table. For example, I have this report thatgets the collected size of all documents within a folder (DTreeAncestors.AncestorID=2000).
select count(distinct docid) as "Number ofDocuments",sum((datasize+ressize)/1000000) as "Total Size(MB)"
from dversdata, DTreeAncestors
where DTreeAncestors.dataid=dversdata.docid andDTreeAncestors.AncestorID=2000
You can just change "2000" to be whatever the toplevel folder node id is.
From: eLink Discussion:Open Text Live Reports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Friday, August 21, 2009 10:46 AMTo: eLink RecipientSubject: Looking For a LiveReport Listing Documents Contained In Folders& SubFolders of Specific Top Level Folder
Looking For a LiveReport Listing Documents Contained In Folders & SubFolders of Specific Top Level Folder
Posted by northo01user2 (Latka, Chad) on 2009/08/21 10:45
Any help appreciated.... What I'm trying to accomplish: I have a root folder with dozens of nested subfolders and then finally some documents at the end. I'm looking to query on the root folder only the documents that have a specific category attribute. The query below times out in SQL and when running the LiveReport , I think because there are too many records. Also I was not able to use the criteria ParentID where IN ('xxxxx') this didn't return any results. ParentID = the root level folder that contains the subfolders of subfolders and finally the documents. %1 is an input for exact string %2 is filter for permissions (not sure if its needed) SELECT DTree.DataID, DTree.Name, DVersData.Version, DVersData.FileName, DVersData.MimeType, LLAttrData.ValStr AS String, DVersData.DocID, DTree.ParentID FROM LLAttrData INNER JOIN DVersData ON LLAttrData.VerNum = DVersData.Version INNER JOIN DTree ON DVersData.DocID = DTree.DataID WHERE ( LLAttrData.DefID = 1234 ) AND ( LLAttrData.AttrID = 2) AND ( DTree.SubType = 144) AND ( LLAttrData.ValStr = '%1') AND %2 ORDER BY LLAttrData.ValStr, DTree.Name
[To reply to this thread, use your normal E-mail reply function.]
Discussion:
Open Text Live Reports Discussion
Livelink Server:
knowledge-wlweb01
To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.
Not to get nit-picky Nicole (well maybe alittle) but your divisor should be 1,048,576 (1024 * 1024).
--
Greg Kellogg
(361) 815-8950
From: eLink Discussion: Open Text Live Reports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Friday, August 21, 2009 8:02AMTo: eLink RecipientSubject: RE Looking For aLiveReport Listing Documents Contained In Folders & SubFolders of SpecificTop Level Folder
RE Looking For a LiveReport Listing Documents Contained In Folders & SubFolders of Specific Top Level Folder
Posted by nbride (Bride, Nicole) on 2009/08/21 11:01
In reply to: Looking For a LiveReport Listing Documents Contained In Folders & SubFolders of Specific Top Level Folder
Message from Bride, Nicole <nbride@cvps.com> via eLink
If you want to get everything from the root folder down, you could try using the ancestor table. For example, I have this report that gets the collected size of all documents within a folder (DTreeAncestors.AncestorID=2000).
select count(distinct docid) as "Number of Documents",sum((datasize+ressize)/1000000) as "Total Size(MB)"
where DTreeAncestors.dataid=dversdata.docid and DTreeAncestors.AncestorID=2000
You can just change "2000" to be whatever the top level folder node id is.
From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Friday, August 21, 2009 10:46 AMTo: eLink RecipientSubject: Looking For a LiveReport Listing Documents Contained In Folders & SubFolders of Specific Top Level Folder
Good point. I grabbed this report from development. I think Ichanged it on production awhile ago.
Thanks for keeping me honest J
-Nicole
From: eLink Discussion:Open Text Live Reports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Friday, August 21, 2009 11:44 AMTo: eLink RecipientSubject: RE RE Looking For a LiveReport Listing Documents Contained InFolders & SubFolders of Specific Top Level Folder
RE RE Looking For a LiveReport Listing Documents Contained In Folders & SubFolders of Specific Top Level Folder
Posted by gkellogg (Kellogg, Gregory) on 2009/08/21 11:43
In reply to: RE Looking For a LiveReport Listing Documents Contained In Folders & SubFolders of Specific Top Level Folder
Message from Greg Kellogg <gkellogg@opentext.com> via eLink
Not to get nit-picky Nicole (well maybe a little) but your divisor should be 1,048,576 (1024 * 1024).
From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Friday, August 21, 2009 8:02 AMTo: eLink RecipientSubject: RE Looking For a LiveReport Listing Documents Contained In Folders & SubFolders of Specific Top Level Folder
<hr
Topic:
Hi Chad,
From what I understand, the DTreeAncestors table holds all ofthe ancestors of a folder.
For example, I have this folder structure:
Enterprise / Projects / Livelink Testing Area / BRP Testing
Here are the node ids for each folder:
2000 / 41088 / 42089 / 58657
Here is what comes back when I run a query against theDTreeAncestor table ( select * from dtreeancestors a where a.dataid = '58657' ):
DataID AncestorID
58657 -1
58657 2000
58657 41088
58657 42089
I just gave you an example of how I used the DTreeAncestortable. I had originally written that query trying to use recursion withthe parentid, but found the DTreeAncestor table easier. Hopefully I will havesome time tomorrow and I can try to incorporate it into your query.
Sorry for the confusion.
From: eLink Discussion:Open Text Live Reports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Monday, August 24, 2009 11:30 AMTo: eLink RecipientSubject: What is the DTreeAncestors table ?
What is the DTreeAncestors table ?
Posted by northo01user2 (Latka, Chad) on 2009/08/24 11:28
In reply to: RE RE RE Looking For a LiveReport Listing Documents Contained In Folders & SubFolders of Specific Top Level Folder
Posted by nbride (Bride, Nicole) on 2009/08/21 11:59
What is the DTreeAncestors table ? Thank you for guidance, however I'm not familiar with the use of the DTreeAncestors table, it must be new to Livelink 9.5. Also wasn't trying to find the data size of items in folders - I'm trying to find documents contained under a root level folder that may also contain other subfolders with documents in it. And was trying to find a way to do that succssfully. - Chad
dTreeAncestors was added in Livelink 9.5. I am uncertain of all of its uses but I do know it speeds browsingconsiderably. And in 9.5.1 it has four indexes on two columns. ColA, ColB andColB, ColA and ColA and ColB (don’t remember the actual column namesnow). In 9.7 we only have two of the four indexes.
From: eLinkDiscussion: Open Text Live Reports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Monday, August 24, 200910:46 AMTo: eLink RecipientSubject: RE What is theDTreeAncestors table ?
RE What is the DTreeAncestors table ?
Posted by nbride (Bride, Nicole) on 2009/08/24 11:45
In reply to: What is the DTreeAncestors table ?
From what I understand, the DTreeAncestors table holds all of the ancestors of a folder.
Here is what comes back when I run a query against the DTreeAncestor table ( select * from dtreeancestors a where a.dataid = '58657' ) :
I just gave you an example of how I used the DTreeAncestor table. I had originally written that query trying to use recursion with the parentid, but found the DTreeAncestor table easier. Hopefully I will have some time tomorrow and I can try to incorporate it into your query.
From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Monday, August 24, 2009 11:30 AMTo: eLink RecipientSubject: What is the DTreeAncestors table ?
Jean-Philippe Martin Conseiller gestion de l'information techniqueGestion de l'information techniqueStratégies et orientations de l'exploitationDirection ExploitationHydro-Québec TransÉnergie514-840-3000 #4977 : martin.jean-philippe@hydro.qc.ca800, de Maisonneuve Est, 22e Montréal, Qc, Canada H2L 4M8
De : eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com] Envoyé : lundi 24 août 2009 12:28À : eLink RecipientObjet : RE RE What is the DTreeAncestors table ? RE RE What is the DTreeAncestors table ? Posted by gkellogg (Kellogg, Gregory) on 2009/08/24 12:27 In reply to: RE What is the DTreeAncestors table ? Posted by nbride (Bride, Nicole) on 2009/08/24 11:45 Message from Greg Kellogg <gkellogg@opentext.com> via eLinkdTreeAncestors was added in Livelink 9.5. I am uncertain of all of its uses but I do know it speeds browsing considerably. And in 9.5.1 it has four indexes on two columns. ColA, ColB and ColB, ColA and ColA and ColB (don’t remember the actual column names now). In 9.7 we only have two of the four indexes. -- Greg Kellogg (361) 815-8950 From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Monday, August 24, 2009 10:46 AMTo: eLink RecipientSubject: RE What is the DTreeAncestors table ? RE What is the DTreeAncestors table ? Posted by nbride (Bride, Nicole) on 2009/08/24 11:45 In reply to: What is the DTreeAncestors table ? Posted by northo01user2 (Latka, Chad) on 2009/08/24 11:28 Message from Bride, Nicole <nbride@cvps.com> via eLink Hi Chad, From what I understand, the DTreeAncestors table holds all of the ancestors of a folder. For example, I have this folder structure: Enterprise / Projects / Livelink Testing Area / BRP Testing Here are the node ids for each folder: 2000 / 41088 / 42089 / 58657 Here is what comes back when I run a query against the DTreeAncestor table ( select * from dtreeancestors a where a.dataid = '58657' ) : DataID AncestorID 58657 -1 58657 2000 58657 41088 58657 42089 I just gave you an example of how I used the DTreeAncestor table. I had originally written that query trying to use recursion with the parentid, but found the DTreeAncestor table easier. Hopefully I will have some time tomorrow and I can try to incorporate it into your query. Sorry for the confusion. -Nicole From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Monday, August 24, 2009 11:30 AMTo: eLink RecipientSubject: What is the DTreeAncestors table ? What is the DTreeAncestors table ? Posted by northo01user2 (Latka, Chad) on 2009/08/24 11:28 In reply to: RE RE RE Looking For a LiveReport Listing Documents Contained In Folders & SubFolders of Specific Top Level Folder Posted by nbride (Bride, Nicole) on 2009/08/21 11:59 What is the DTreeAncestors table ? Thank you for guidance, however I'm not familiar with the use of the DTreeAncestors table, it must be new to Livelink 9.5.Also wasn't trying to find the data size of items in folders - I'm trying to find documents contained under a root level folder that may also contain other subfolders with documents in it. And was trying to find a way to do that succssfully. - Chad [To reply to this thread, use your normal E-mail reply function.] Topic: Looking For a LiveReport Listing Documents Contained In Folders & SubFolders of Specific Top Level Folder Discussion: Open Text Live Reports Discussion Livelink Server: knowledge-wlweb01 To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.
What is the DTreeAncestors table ? Thank you for guidance, however I'm not familiar with the use of the DTreeAncestors table, it must be new to Livelink 9.5.Also wasn't trying to find the data size of items in folders - I'm trying to find documents contained under a root level folder that may also contain other subfolders with documents in it. And was trying to find a way to do that succssfully. - Chad
Hey,
Are you using Oracle?
From: eLink Discussion:Open Text Live Reports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Monday, August 31, 2009 6:38 AMTo: eLink RecipientSubject: Two different sets of answers for the count queries
Two different sets of answers for the count queries
Posted by dpeadmin (EDMS ADMIN, DPE) on 2009/08/31 06:36
Hi Nicole, I used your query to grab the total number of docs in a folder and its subfolders. The answer differed to the extend of 20,000 to a different query I used to use before. Here is that query. Which is more accurate? select count(*) from (select level,parentid,dataid,name,subtype,a.CHILDCOUNT from dtree a start with dataid = %1 connect by prior dataid = parentid )
Hello all,
There were inconsistencies in theDTreeAncestors data due to product defects in older codelines (< January2009).
The more recent monthly patches shouldinclude fixes for these issues; however, the DTreeAncestors table would need tobe rebuilt (enable/disable the Recommender agent in 9.7.1) for this to affect tableentries for items that have not been relocated under the patched code.
-- Kyle
From: eLinkDiscussion: Open Text Live Reports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com] Sent: Monday, August 31, 2009 9:54AMTo: eLink RecipientSubject: I could be severelymistaken on this but dtree registers every valid node in...
I could be severely mistaken on this but dtree registers every valid node in...
Posted by anair@alitek.com (Nair, Appu) on 2009/08/31 09:52
In reply to: RE Two different sets of answers for the count queries
Posted by nbride (Bride, Nicole) on 2009/08/31 08:04
I could be severely mistaken on this but dtree registers every valid node in the livelink database.Simply put every action to create,update,delete all centers around dtree so it is almost impossible to blame dtree for missed operations.On the other hand the dtreeancestors table is a utility table that is created/maintained by oscript code so unless every error checks on dtreeancestors was made a critical error could it miss a few ,I do not know.David Templeton has recently said in a recent posting that the dtreeancestors table is maintained by node callbacks so in a perfect world it should register a one to one match. I would in your connect by clause base it on abs(dataid)=abs(parentid) so that the dtree based query is actually counting things in CD's,Projects,Discussions etc that have negative volume concepts and cross check the count based on dtreeanacestors.It is well possible that the good code in dtreeancestrors has counted stuff within volumes as well.
