Folder holding how many doc's and its size in MB

Hi guys,I am trying to write a livereport that gives details for a folder as of windows style (i.e.,when right clicks it show no of docment inside and space acquired in MB) when provide an object id (or dataid) for a particular container.i know this....select count(datasize)"File count" from dversdata where docid in (select dataid from dtree start with dataid in () connect by prior dataid=parentid)I want to know corresponding document size beside its count.Thanks in advance.

Find more posts tagged with

Comments

Tim_Hunter

This will give you the count of the files, the size in MB of the current version of everything in a folder SELECT COUNT(d3.docid) AS "Number of Docs", SUM(d3.datasize) /(1024 *1024) "Megabytes" FROM dtree d1, dtree d2 , dversdata d3 WHERE d1.dataid = d3.docidAND d1.versionnum = d3.versionAND d1.parentid = d2.dataidAND d1.subtype IN(0, 144)AND d1.dataid IN (SELECT dataid FROM dtree START WITH dataid = CONNECT BY PRIOR dataid = ABS(parentid) )

Chukwudi_Awaibe

Hello,Will this also count subfolders and subprojects. I getNumber of Docs = 0Megabytes = ?When I run the report But I have a lot of documents and subfolders in the folder. Am running LL 9.7.1 on oracle 10g.

Gail_Steinhobel

Hi ... I tried the query but it doesn't work on our system because apparently the CONNECT BY PRIOR is an oracle statement and not SQL. can you assist with the correct SQL statement.Thanks

Bhupinder_Singh

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

For SQL Server then you need to use a stored procedure in order the generate a report that includes any level of sub-folders.

You can search for SQL server stored procedure examples on the Knowledge Center web site. There are examples to be found such as the link below.

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

- Bhupinder

----------------------------------------------
Bhupinder Singh, B.Math, B.Ed.
Senior Systems Analyst, Information Technology
Open Text, Waterloo, Ontario, Canada
----------------------------------------------

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, August 27, 2008 5:26 AM
To: eLink Recipient
Subject: Oracle vs SQL

Oracle vs SQL		Posted by gail.steinhobel@sasol.com (Hardy, Gail) on 08/27/2008 05:23 AM
In reply to: RE Folder holding how many doc's and its size in MB		Posted by timothy.hunter.ctr@wpafb.af.mil (Hunter, Tim ) on 07/01/2008 09:45 AM

Hi ... I tried the query but it doesn't work on our system because apparently the CONNECT BY PRIOR is an oracle statement and not SQL. can you assist with the correct SQL statement.
Thanks

Tim_Hunter

This will not count subprojects, but does count subfolders and everything in them, to count projects you need to add the appropriate subtype (0 = folder, 144=document, 202=project)