Want a report of folder, the number of docs and the size of folder

I want a report to show all of the folders in Enterprise, the total number of documents and the size of all the documents in that folder.Here is the SQL I came up with. Would someone look at this and tell me if the SQL will give me the desired result.SELECT LPAD('-',4*(LEVEL-1),'-')|| outer.name "Folder Name", (SELECT DECODE(COUNT(1),0,0,COUNT(1)) FROM dtree inner WHERE inner.parentid=outer.dataid and SUBTYPE=144) "No. of Docs", (SELECT DECODE(SUM(v.datasize)/(1024 *1024),NULL,' ', ROUND(SUM(v.datasize)/(1024 *1024),2)) FROM dversdata v, dtree inner2 WHERE inner2.dataid = v.docid AND inner2.versionnum = v.version AND outer.dataid = inner2.parentid) "Megabytes" FROM dtree outer WHERE outer.subtype = 0 START WITH outer.dataid = 2000 CONNECT BY PRIOR outer.dataid = abs(outer.parentid) UNION ALL SELECT 'Totals', COUNT(1), TO_CHAR( ROUND(SUM(v.datasize)/(1024 * 1024),2)) FROM dtree d, dversdata v WHERE d.dataid = v.docid AND d.versionnum = v.version

Find more posts tagged with

Comments

Tim_Hunter

Just a few comments.Technically this will work, although you will punish your DB for awhile depending on how big your Enterprise workspace is and how deep it runs.FYIsNo. of Docs will only count subtype 144, which might be 0 even if there are other types in thereThe Megabytes reported will report on those other subtypes, not just 144The megabytes only counts the current folder, not subfolders, might be misleading in the report, but if you change the query, it will greatly increase the query cost

Ritchie_Gillam

Tim,Thanks for looking at this query for me. Any suggestions on the following:1) How to get the Megabytes to report on just subtype 144?2) How to greatly improve the efficiency of the query?Thanks in advance,Ritchie