5 Replies Latest reply on Oct 10, 2018 5:06 PM by Clark Honzik

    SolidWorks PDM SQL query

    David Hales

      I'm far from a DBA, which is why I have been tying for a couple days to figure out how to get the info that I need.  This is the query I have so far.  I want to add the date of the last version (TransitionHistory.Date).  I thought I could join Documents.LatestRevisionNo = TransitionHistory.RevNr, but I can't get it to work.  I end up getting a huge groupwise maximum and can't overcome it.  Can anyone help?

       

      SELECT d.Filename,ISNULL(u.FullName,'') AS CheckedOutBy,fe.Extension AS FileType,s.Name AS State

      FROM Documents d

          INNER JOIN Users u ON u.UserID = d.UserID

          INNER JOIN FileExtension fe ON fe.ExtensionID = d.ExtensionID

          INNER JOIN Status s ON s.StatusID = d.CurrentStatusID

          INNER JOIN Workflows w ON w.WorkflowID = s.WorkflowID

      WHERE w.WorkflowID <> '1'

          AND d.Deleted = '0'

          AND d.ExtensionID IN ('3','4','5')

          AND d.Filename NOT LIKE '%^%'

          AND d.Filename NOT LIKE '%<%'

      ORDER BY d.Filename