Hi all,
I have this query that returns file names, file path and workflow state of files in the Vault that Joy Garon posted in this thread: Queying SQL Database Basics
SELECT P.Path As 'Project Path', D.Filename AS 'File Name', S.Name AS 'Status Name' FROM Projects AS P INNER JOIN DocumentsInProjects AS DP ON P.ProjectID = DP.ProjectID INNER JOIN Documents AS D ON DP.DocumentID = D.DocumentID INNER JOIN Status AS S ON D.CurrentStatusID = S.StatusID Where (S.Name LIKE '%') AND D.ObjectTypeID <> 0 Order By S.Name ASC
I was wondering if anyone could point me in the right direction to show specific variables like Revision, Description, etc..
I tried this:
select documents.Filename, status.Name, LatestRevisionNo
from documents
join status
on status.StatusId = documents.currentstatusid
But it doesn't return the Revision I expected
Any advice would be much appreciated!
First query correct variable id for variable to use in your query.
SELECT [VariableID]
,[VariableName]
,[VariableType]
,[IsDeleted]
,[FlagUnique]
,[FlagMandatory]
,[FlagFreeUpdateAllVersion]
,[FlagFreeUpdateLatestVersion]
FROM [VAULT_NAME].[dbo].[Variable]
where variablename like '%revision%'
Then innerjoin VariableValue table to your query earlier query
Then add valuetext from variablevalue table to select.
Example:
Good way to test your queries is using SELECT TOP 10 to only select top 10 rows, makes testing much faster.
Another way you can use is find some known file from PDM and use it in WHERE clause. Example:
Where DP.Deleted = 0 and VV.VariableID = 61 and D.Filename like 'filename.slpdrt'
I have added here to Distinct to skip duplicate rows.
This will query all revisions of file by that variable, so you still have probably some work to do.
SELECT DISTINCT P.Path As 'Project Path', DP.DocumentID as DocumentID, D.Filename, VV.ValueText
FROM Projects AS P INNER JOIN
DocumentsInProjects AS DP ON P.ProjectID = DP.ProjectID
INNER JOIN
Documents as D ON D.DocumentID = DP.DocumentID
INNER JOIN
VariableValue as VV ON VV.DocumentID = D.DocumentID
Where DP.Deleted = 0 and VV.VariableID = 61