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!
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?
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.
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 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.
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.