8 Replies Latest reply on Jul 19, 2011 2:10 PM by Vlad Krylov

    SQL querry help (system rev)

    Jeremy Feist

      we have a similar issue as this thread https://forum.solidworks.com/message/185296#185296.

       

      Ideally, I woud want to edit the sample querry in that comes with the EPDM install "File details of all files in a project" in 2 ways;

      1) make it recursively go through sub-folders.

      2) have it list the system rev, along with a couple of variables for each file

       

      if it can't be recursive, then I would look at the "DocumentData of selected files" querry and get it to show the system rev and other variables.

       

      I have attached a crp with just those 2 querries for reference, but I have zero experience in SQL, so any and all help would be much appreciated.

       

      thanks,

       

      edit - I noticed that I had attached the crp before saving it - now irt only has the 2 querries I am interested in.

        • Re: SQL querry help (system rev)
          Lee CS Young

          Here is a starting point. I don't have the time at the moment to add the variables and make it a report, but it should give you a starting point. The @id at the beginning of the query is the ID of the root folder you're looking in. The revision listed is the system revision.

           

          DECLARE @id int
          SET @id = 1; --ID of root folder
          --CTE for recursive lookup
          WITH InnerProjects (ProjectID, ChildProject, Level)
          AS
          (
          SELECT 
               pt.ProjectID
              ,pt.ChildProject
              ,1 AS Level
          FROM ProjectTree pt
          
          UNION ALL
          
          SELECT 
               pt.ProjectID
              ,pt.ChildProject
              ,Level + 1
          FROM ProjectTree pt
           INNER JOIN InnerProjects ip ON pt.ChildProject = ip.ProjectID
          
          )
          --First select the root folder's documents.
          SELECT  
               IP.ProjectID
              ,0 AS ChildProject
              ,D.DocumentID
              ,D.Filename 
              ,P.Name    
              ,P.Path
              ,UR.Comment as Revision
          FROM InnerProjects IP
          INNER JOIN DocumentsInProjects DP ON DP.ProjectID = IP.ProjectID
          INNER JOIN Documents D ON D.DocumentID = DP.DocumentID
          INNER JOIN Projects P ON P.ProjectID = IP.ProjectID
          LEFT JOIN UserRevs UR ON UR.DocumentID = D.DocumentID AND UR.RevNr = D.LatestRevisionNo
          WHERE IP.ProjectID = @id
          AND D.Deleted = 0
          UNION --now union the child folder docs.
          SELECT   
               IP.ProjectID
              ,IP.ChildProject
              ,D.DocumentID
              ,D.Filename 
              ,P.Name
              ,P.Path
              ,UR.Comment as Revision
          FROM InnerProjects IP
          INNER JOIN DocumentsInProjects DP ON DP.ProjectID = IP.ChildProject
          INNER JOIN Documents D ON D.DocumentID = DP.DocumentID
          INNER JOIN Projects P ON P.ProjectID = IP.ChildProject
          LEFT JOIN UserRevs UR ON UR.DocumentID = D.DocumentID AND UR.RevNr = D.LatestRevisionNo
          AND D.Deleted = 0
          ORDER BY ChildProject, DocumentID
          
          • Re: SQL querry help (system rev)
            Vlad Krylov

            Normally, you would need a recursive function to extract all tree child nodes from a ProjectTree type table in a way Lee suggested. However with Projects table there is a shortcut, albeit the one which will be frowned upon by purists. You can take advantage of the Path field of the Projects table and retrieve all child projects of a [parent project] by simply:

             

            SELECT Projects.ProjectID
                FROM Projects
                WHERE Projects.Path LIKE '[parent project folder path]%' AND
                      Projects.Deleted = 0

             

            Now that you have a list of projects getting all the documents stored in them is:

             

            SELECT Documents.DocumentID, Documents.Filename 
                FROM Documents INNER JOIN DocumentsInProjects ON Documents.DocumentID = DocumentsInProjects.DocumentID 
                WHERE Documents.Deleted = 0 AND       
                      DocumentsInProjects.ProjectID IN (                
                                SELECT Projects.ProjectID                  
                                    FROM Projects               
                                    WHERE Projects.Path LIKE '[parent project folder path]%' AND                     
                                           Projects.Deleted = 0                                                 
                                               ) 

             

            The above will just return file ids and names, but now that you have file ids you can inner join on other tables to retrieve other data associated with the document

             

            EDIT: July 19th...

             

            Upon further review of EPDM Database structure, there is a table ProjectTreeRec which stores parent-child relationship between all levels of folders. This table can be used to query the list of all files in a given folder and all its subfolders as follows:

             

            SELECT Documents.DocumentID,Documents.Filename,Documents.LatestRevisionNo
              FROM Documents INNER JOIN DocumentsInProjects ChildProjects ON Documents.DocumentID = ChildProjects.DocumentID
                               INNER JOIN ProjectTreeRec ON ChildProjects.ProjectID = ProjectTreeRec.ChildProjectID
                               INNER JOIN Projects ParentProject ON ProjectTreeRec.ParentProjectID = ParentProject.ProjectID
              WHERE ParentProject.Path = '[parent project]' AND
                       Documents.Deleted = 0