How to return ALL documents (including all subdirectories) under Enterprise root folder?

Hi,What is the best way to return ALL documents (including all subdirectories) under Enterprise root folder?I was reading one of the thread here that has LL_PATH function (very useful, thanks!) and so I add in the criteria substr(LL_PATH(D.DataID),1,10) = 'Enterprise" but the query performance is very poor (3-5 minutes for less than 5 rows returned).

Find more posts tagged with

Comments

Martin_Gäckler

What databse are you using?In Oracle I would use something linke thisselect * from dtreewhere subtype=144connect by prior dataid = parentidstart with dataid=2000Hope this helps.Martin

Jean-Philippe_Martin

Message from <Martin.Jean-Philippe@hydro.qc.ca> via eLink

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

I think this one will be much faster :

select *
FROM lvora1a.dtree
where ownerid = -2000 and subtype =144

Please note that this won't go into project folders and discussion since their ownerid change.

Jean-Philippe Martin
Conseiller gestion de l'information technique

Gestion de l'information technique
Stratégies et orientations de l'exploitation
Direction Exploitation
Hydro-Québec TransÉnergie

514-289-2211 #2949 : martin.jean-philippe@hydro.qc.ca
2 Complexe Desjardins, Tour Est, Étage PR, Montréal, Qc, Canada H5B 1H7

De : eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Envoyé : vendredi 30 avril 2010 04:55
À : eLink Recipient
Objet : RE- How to return ALL documents (including all subdirectories) under Enterprise root folder?

RE- How to return ALL documents (including all subdirectories) under Enterprise root folder? Posted by mgaeckler (Gäckler, Martin R) on 2010/04/30 04:53
In reply to: How to return ALL documents (including all subdirectories) under Enterprise root folder? Posted by skrisman@visteon.com (Sumartini, Krisman) on 2010/04/28 10:20

What databse are you using?

In Oracle I would use something linke this

select * from dtree
where subtype=144
connect by prior dataid = parentid
start with dataid=2000

Hope this helps.

Martin

Sumartini_Krisman

Thank you! This works perfectly.