I'm developing some SQL Server queries to get data from the ProjectWise back end.
One item that I'm looking for is the path for a given project folder. Using the dms_proj table, I can get the folder name as dms_proj.o_projectname, and find the parent folder by looking up the dms_proj.o_parentno value in the dms_proj.o_projectno column. However, I'm wondering if there's a more efficent way to get the folder path, rather than doing a long chain of dms_proj.o_parentno lookups. I've gone through the dms_* table set but haven't found a table that has the folder paths; does one exist?
In ORACLE this can be done with Start With / Connect By, in SQL it is more complicated but can be done according to this post : stackoverflow.com/.../simulation-of-connect-by-prior-of-oracle-in-sql-server To my knowledge there is not a table that stores every path to a document in report like form.
I found a potential solution to this here:
Create A Database View In SQL Server That Will Contain The Path For A Given “Folder Id”