SQL Report for all document below a particular folder in the tree

Question

Hi all 
 I'm trying to develop an SQL query that would list all documents below and including a folder (or work area) in projectwise, irrespective of how many subfolders down they are. 
 I've figured out how I could do it all the files where in folders where only one level below the parent (see my initial thoughts below) but I'm not sure how about getting it to include level 2, 3 and 4 etc. etc. without prespecifing the folder id's: which I don't want to do since they won't necessarily be static. 
 
 ( 
 select o_docguid, o_filename, dms_doc.o_projectno, dms_proj.o_parentno, o_itemname, o_itemno, o_itemdesc from [dms_doc] FULL OUTER JOIN [dms_proj] on dms_doc.o_projectno = dms_proj.o_projectno where dms_proj.o_parentno = '1907' 
 ) 
 
 Cheers 
 
 Matt

Jeff Burrill · Accepted Answer

Your outer join will only include the first level of sub-folders under the folder. You would have to do some form of a recursive query to get all the possible sub-folders beyond the first level. There are different ways to go about that, depending on your database platform. 
 Have you considered PowerShell? Check out https://communities.bentley.com/products/projectwise/content_management/f/projectwise-powershell-extensions-forum There are some cmdlets in there that may get you what you need, and some have the ability to recurse sub-folders as part of the processing.

SQL Report for all document below a particular folder in the tree

Top Replies