I have been trying to figure out how do determine the path to a given folder with a SQL query for a long time. Finally I had a project where I really need this ability so I searched online for something similar.
Eventually I found this: Dump list of full paths of parent/child type records in SQL Server 2008 R2
Which I changed to look like this:
USE PWDemoMSSQLGOWITH folder_paths (o_projectno, folder_path)AS(-- Anchor member definition SELECT f.o_projectno, cast('\'+f.o_projectname as varchar(max)) as folder_path FROM dbo.dms_proj AS f WHERE o_parentno=0 UNION ALL-- Recursive member definition SELECT f.o_projectno, cast(folder_path + '\' + f.o_projectname as varchar(max)) FROM dbo.dms_proj AS f INNER JOIN folder_paths AS fp ON f.o_parentno = fp.o_projectno)-- Statement that executes the CTESELECT o_projectno, folder_pathFROM folder_paths ORDER BY folder_pathGO
Here’s what it looks like along with the corresponding folder in ProjectWise Explorer:
To make this a bit more useful, I created a database view. Here’s what I used:
USE PWDemoMSSQLGOCREATE VIEW vFolderPath ASWITH folder_paths (o_projectno, folder_path)AS(-- Anchor member definition SELECT f.o_projectno, cast('\'+f.o_projectname as varchar(max)) as folder_path FROM dbo.dms_proj AS f WHERE o_parentno=0 UNION ALL-- Recursive member definition SELECT f.o_projectno, cast(folder_path + '\' + f.o_projectname as varchar(max)) FROM dbo.dms_proj AS f INNER JOIN folder_paths AS fp ON f.o_parentno = fp.o_projectno)SELECT o_projectno, folder_pathFROM folder_pathsGO
So now I can query for a specific folder like this:
SELECT folder_path FROM vFolderPath WHERE o_projectno=114;
For my purposes, I didn’t bother adding an index, but you might want to add one depending upon your needs. I suspect that something like this could be done with Oracle as well. I’ll leave that for another day!
Apologies to dig up an old post, but this query was incredibly helpful for us!
Is it possible to change the query so that it works the other way? I.e. from the bottom folder up instead of Folder 0 down?
Reason is that when querying the view, it takes around 45 seconds to load due to the number of folders within our Datasource. So if I'm trying to write a query using this view to grab multiple file paths it can take minutes to load, during this time the users are freaking out that ProjectWise has crashed.
I'm hoping that if its only 1 folder path being built it would be a lot quicker. I have tried to modify the query myself but i cant quite seem to get it working.
Thanks!
Frank, as a side note, I encourage you to post future questions in the respective Forum, to ensure that you receive a timely response (referencing a wiki article or blog post if needed). Comments on a blog post will only alert the author of the post by default, whereas the forums are monitored by many.
Frank,
I tested this on a datasource and it is clear that your problem is that you are using quotes for a numeric property. Drop the quotes and it will work.
all good i thought the same so i replaced the $VAULT$ with an actual number like below.
select folder_path FROM vFolderPath WHERE o_projectno="6901"
I think the environment attribute doesnt like the vFolderPath view because its not a table, i think it may need a different syntax?
I can run the select statement in the sql server management studio and i can get the folder path.
I suspect that you need to remove the single quotes from $VAULT$ as o_projectno is a number, not a string. You could probably also try to convert the value of $VAULT% with a CAST or CONVERT function.
Hi Dan,
This is great!! I have created the view but I'm having issues using it.
I copied the above statement and use vault system variable as folder id. The SELECT folder_path FROM vFolderPath WHERE o_projectno='$VAULT$' is pasted into an attribute default and update value but i'm getting an error, table column doesn't exist when i'm updating.
Do you know how i should be using it?
ThanksFrank