3 Replies Latest reply on Sep 21, 2012 12:01 PM by Eric Endlich

    Query variables from older versions

    Derek Hopkins

      I am trying to write a sql query to list all variables of a Solidworks model based on a specific version.  For instance, if the model has 20 versions I want to know what the variables were in version 5 or maybe 10.  I do not see a way to link the variables to specific versions or the file.  How is this done?

       

      Thank you.....

        • Re: Query variables from older versions
          Lee CS Young

          If you look at the Report Examples.crp file in your EPDM installation directory there is a query that will lead you down the right path. (DocumentDataOfSelectedFiles).

           

          The RevisionNo column in the VariableValue table is what you're concerned with.

          • Re: Query variables from older versions
            Ravi Teja

            Hi Derek,

               See the query.

             

             

            SELECT     TOP 1 Documents.Filename, Variable.VariableName, VariableValue.ConfigurationID, VariableValue.RevisionNo

            FROM         Documents INNER JOIN

                                  VariableValue ON Documents.DocumentID = VariableValue.DocumentID INNER JOIN

                                  Variable ON VariableValue.VariableID = Variable.VariableID

            WHERE     (Documents.Filename LIKE N'%Your File Name here%') AND (VariableValue.RevisionNo <= 'Version Number') AND (Variable.VariableName LIKE N'%Variable Name%')

            order by RevisionNo DESC

             

             

             

            Above query fetches VariableVariable Value from Specified.

            EPDM Saves a VariableValue only for the version in which it changes.

             

            So in this query we have to enter the version ID.and the query will generate the list of variable values for the versions less than the entered nuber and show the top most value.

             

            But the pronlem is it will show only one Config,You can modify the query to show retrieve and show specifi config.

             

             

             

             

            Regards

            Ravi T

            • Re: Query variables from older versions
              Eric Endlich

              Heres a query that will list out the variables that have changed. It does not list out all variables for each revision but since the variable hasn't changed it is still the same from the previous revision(s). It will also return nothing if the file does not have any variables associated with it. Just replace <FILENAME> with the name of the file.

               

              Declare @FileName as nvarchar(255)

              Set @FileName='<FILENAME>'

              SELECT documents.DocumentID,Filename,LatestRevisionNo, Revisions.RevNr, VariableValue.RevisionNo, VariableName, VariableValue.ValueCache, Documents.Deleted FROM [Documents]

              inner join Revisions on Revisions.DocumentID=documents.DocumentID

              inner join VariableValue on VariableValue.DocumentID=Documents.DocumentID

              inner join Variable on Variable.VariableID=VariableValue.VariableID

              Where Revisions.RevNr=VariableValue.RevisionNo and documents.Filename=@FileName

              order by documents.DocumentID, Revisions.RevNr