Urgently Need Help on query for size of folder and subfolders

Sugaindra_krishnakumaran

Hi, can someone check on the query as below and pls let me know on where i've done wrong. Im tring to get the results which will list size of all folders and subfolders which also includes the project workspace. I am only getting the size of folders and subfolders but the project workspace is not being listed.select d1.parentid "ParentID",d1.dataid "ObjectID",d1.name, round(sum(dv.datasize/1048576),0) "Size in MB" from dversdata dv, dtree d1,dtree d2where d2.parentid=d1.dataid and d2.dataid = dv.docid and d2.versionnum=dv.version and d1.subtype in (0,202) andd1.dataid in (select dataid from dtree start with dataid=#### connect by prior dataid=abs(parentid))group by d1.parentid,d1.dataid, d1.name order by d1.parentid

Appu_Nair

select d1.parentid "ParentID",d1.dataid "ObjectID",d1.name, round(sum(dv.datasize/1024),0) "Size in MB" from dversdata dv, dtree d1,dtree d2where abs (d2.parentid)=abs(d1.dataid) and d2.dataid = dv.docid and d2.versionnum=dv.version and d1.subtype in (0,202) andd1.dataid in (select dataid from dtree start with dataid=306074 connect by prior abs(dataid)=abs(parentid))group by d1.parentid,d1.dataid, d1.name order by d1.parentid the Project subtype has negative dataid as well,so it is really the abs on this line where abs(d2.parentid)=abs(d1.dataid) that makes it work.the prior abs(dataid)=abs(parentid) will work without abs'ing on the dataid.I cannot explain the logic or efficiency of this query but ABS'ing has problems such as it would not use the indexes and stuff like that so it is better if you can use dtreeancestors somehow to write this query for efficiency.