AnsweredAssumed Answered

Modifying SQL Query for specific PDM Variables

Question asked by John Power on Feb 7, 2019
Latest reply on Feb 11, 2019 by John Power

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!

Outcomes