Number of Documents in a Folder Tree

I find this one a bit challenging. I need a report that shows the number of documents in a folder tree. In other words, not just the children, but also their children and grand children, etc. I am envisaging a recursive query, with a lot of inner joins on DTree.We run Microsoft SQL Server 2000Any suggestions?

Find more posts tagged with

Comments

Magnus_S_(novnoruser_-_(deleted))

https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=2349969&objAction=view&show=2

volvostephen

Message from via eLinkin SQL Server you can only accomplish this with recursive calls. I usetemporary tables and then join the temp table to dtree or use the temp tabledirectly. Not ideal but it works. This isn't meant to be run excessivelyas it is horrible for performance....You can add or remove columns from the #tmp table as necessary or join the#tmp table to whatever. FUllPath isn't implemented here but I have otherposts on the KC about a SQL Function that returns Full path (and is veryfast but still horrible for performance). The (ABS) is used becausedataID's can flip from + to - when you go into a project.Hope this is useful. There are other posts here by me on this topic. GoodluckDeclare @DataID int Declare @Count int Declare @SQL varCHar(8000)set nocount on set @DataID = 6063 /* this is the dataID of the folder when you want allchildren */set @Count = 1 CREATE TABLE #Tmp ( ID int NOT NULL IDENTITY (1, 1), FolderLevel int NOT NULL, Name varChar(255), DataID int NOT NULL, ParentID int NOT NULL, [Permissions] int, Access int, ChildCount int, SubType int, [CreateDate] [datetime] NULL , [ModifyDate] [datetime] NULL , FullPath varChar(8000) ) ON [PRIMARY] While @Count < 50 /* sanity check - this doesn't go more then 50levels deep (protects from infinite loops on bad data) */ Begin Set @SQL = 'insert into #tmp select ' + cast(@Count asvarchar(50)) + ', Name, DataID, ParentID,0,0,ChildCount,SubType,[CreateDate], [ModifyDate],NULL from dtree with (nolock) ' if @Count = 1 Set @SQL = @SQL + 'where dataID = ' + Cast(@DataIDas varchar(50)) else Set @SQL = @SQL + 'where ABS(ParentID) in (selectdataID from #tmp where FolderLevel = ' + cast(@Count-1 as varchar(50)) + ')' --print @SQL exec (@RowCount = 0 BREAK ELSE Set @Count = @Count + 1 end -----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: September 14, 2004 6:36 AMTo: eLink RecipientSubject: Number of Documents in a Folder TreeNumber of Documents in a Folder TreePosted by slaghuis, Eric on 09/14/2004 06:27 AMI find this one a bit challenging. I need a report that shows the number ofdocuments in a folder tree. In other words, not just the children, but alsotheir children and grand children, etc. I am envisaging a recursive query,with a lot of inner joins on DTree.We run Microsoft SQL Server 2000Any suggestions?[To reply to this thread, use your normal E-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

Dave_Ebels_(jocoadmin_-_(deleted))

Data size in K bytes, number of files in a folder & sub folders and average file size (is the name of the report) The report returns what the title implies The set up is ,,,,Record limit 10000 (makes me feel good to use a large number)Inputs ,, Number (tag) Please enter an object IDSQLselect round(sum(datasize)/1024,3)"Data Size (in K bytes) ",count(datasize)"File count",(round(sum(datasize)/1024,3)/count(datasize))"Average file size (in K bytes)" from dversdata where docid in (select dataid from dtree start with dataid=%1 connect by prior dataid=parentid)Param %1 is User input 1Setup as Auto Live Reportoutput example Data Size (in K bytes) File count Average file size (in K bytes) 5056 8 632 Dave EbelsJohnson Controlsdave.j.ebels@jci.com

Eric_Slaghuis_(sasoladmin_-_(deleted))

I needed code like this again, but when I run this command Livelink simply states "No results found". Is it just me or is there a problem with this code? Is someone actually running this on a Livelink server?

***_****_(citlon01admin_-_(deleted))

In Oracel we created a view that lists the tree. Then filter based on the folder type.Create or Replace view COL_TreeView as select dataid, level TreeLevel, lpad(' ',2 * level -1)|| Name Namefrom dtreestart with dataid = 2000connect by parentid = prior dataidUsing this view the used can view the record as a tree.There is no equivalent to the command in MS SQL, (MS might grow up in later versions), but the following link called "Working with hierarchical data in SQL Server databases" describes the technique.http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htmDavid LoffhagenCity of London