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.
selecto_docguid,o_filename,dms_doc.o_projectno,dms_proj.o_parentno,o_itemname,o_itemno,o_itemdescfrom [dms_doc]FULL OUTER JOIN [dms_proj] on dms_doc.o_projectno = dms_proj.o_projectnowhere dms_proj.o_parentno = '1907'
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.
Please note that I post here on a voluntary basis and am not a Bentley employee.
Answer Verified By: Gayatri Belhekar