9 Replies Latest reply on Feb 11, 2019 5:26 PM by John Power

    Modifying SQL Query for specific PDM Variables

    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!

        • Re: Modifying SQL Query for specific PDM Variables
          Eero Luotio

          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

            • Re: Modifying SQL Query for specific PDM Variables
              John Power

              Thanks Eero that worked quite well, and thank you everyone for your help and suggestions I really appreciate it!

               

              One last question... Is it possible to have multiple Table Headings to query for extra columns? eg..

              VariableValue as VV ON VV.DocumentID = D.DocumentID

              Where DP.Deleted = 0 and VV.VariableID = 61

              VariableValue as DD ON DD.DocumentID = D.DocumentID

              Where DP.Deleted = 0 and VV.VariableID = 43

               

              =====

              Also while doing some research on the forums I came across this where you can let PDM Build Queries for you:

               

              1) Create the column set for the fields you want returned

              2) Run the search (C:\Program Files\SOLIDWORKS Corp\SOLIDWORKS PDM\search.exe) as administrator

              3) Change the registry setting:

              Create a new DWORD value called DebugSQL under HKEY_LOCAL_MACHINE\SOFTWARE\SolidWorks\Applications\PDMWorks Enterprise Set it to 1 When running a search, the search query will be created in a file called c:\search.txt

              4) Paste the contents of c:\search.txt into a new query.

               

              Every time a search is run,  c:\search.txt will be updated with the new query.

              ====

            • Re: Modifying SQL Query for specific PDM Variables
              Martin Solem

              Just to add to the confusion: Revisions in the database are versions, so as Eero Luotio points out, you're better off getting the variable value.

              • Re: Modifying SQL Query for specific PDM Variables
                Charley Saint

                Hey John,

                 

                I use this report all the time. It's pretty straightforward on how to add variable columns and filter based on path or specific configurations. It's not the fastest report in the world which could be fixed but then it'd be much harder to configure, hope it helps.

                 

                declare @report_cols as varchar(500)
                set @report_cols = '[Description], [Part Number]' -- maintain this format no comma after last entry
                
                
                declare @filter_path as varchar(250)
                set @filter_path = '\Projects\' -- don't forget trailing slash, for entire vault use just \, but for speed limit it to certain folders
                
                
                declare @filter_configs as varchar(250)
                set @filter_configs = '(''@'', ''Default'')' -- maintain this format, comment below to not filter configs, for just @ use '(''@'')'
                
                
                declare @sql as varchar(max)
                set @sql = 'with maxvar as
                (select documentid, configurationid, variableid, max(RevisionNo) revisionno from VariableValue
                group by DocumentID, ConfigurationID, VariableID)
                
                
                select path, filename, ConfigurationName, ' + @report_cols + ' -- replace , with + to not split up file and path
                from (
                select p.path, filename, dc.ConfigurationName, variablename, vv.valuecache
                from documents d
                inner join DocumentsInProjects dp on d.DocumentID = dp.DocumentID
                inner join projects p on p.projectid = dp.ProjectID
                inner join ProjectTreeRec ptr on ptr.ChildProjectID = dp.ProjectID
                inner join Projects p2 on p2.ProjectID = ptr.ParentProjectID
                inner join VariableValue vv on vv.DocumentID = d.DocumentID
                inner join maxvar mv on vv.DocumentID = mv.DocumentID and vv.ConfigurationID = mv.ConfigurationID
                and vv.VariableID = mv.VariableID and vv.RevisionNo = mv.revisionno
                inner join variable v on v.VariableID = vv.VariableID
                inner join DocumentConfiguration dc on dc.ConfigurationID = vv.ConfigurationID
                where 
                d.Deleted = 0 and dp.Deleted = 0  -- ignore deleted files
                and p2.path = ''' + @filter_path + ''' -- filter to only files in a specific folder
                and dc.configurationname in ' + @filter_configs + ' -- comment out this line to return all configs
                ) a
                pivot(
                max(valuecache)
                for
                variablename in (' + @report_cols + ')
                ) b'
                
                
                execute(@sql)