3 Replies Latest reply on Dec 24, 2014 8:26 AM by Iftach Priel

    List from SQL Database that looks at all configurations / sheets

    Iftach Priel

      I'm trying to set up a list for search cards that is derived from SQL database. The list should select distinct values of a specific variable ("CurRevECO" ).

      It works, but it brings back only some of the values. looking into it, I noticed that it does'n't select values from the @ sheet (drawings).

      Is there a way to select values from all configurations \ sheets?

      Alternatively, is there a way to select values only from @ configuration?

      this is the SQL code I'm using:

       

      SELECT DISTINCT VariableValue.ValueCache

      FROM Documents INNER JOIN

           VariableValue ON Documents.DocumentID = VariableValue.DocumentID INNER JOIN

           Variable ON VariableValue.VariableID = Variable.VariableID

      WHERE (Variable.VariableName LIKE 'CurRevECO') 

           AND Documents.LatestRevisionNo = VariableValue.RevisionNo 

      Order by  VariableValue.ValueCache DESC

        • Re: List from SQL Database that looks at all configurations / sheets
          Jason Capriotti

          ConfigurationID=4 is the "@" configuration name. If you didn't filter it out, its values should show. Here's an updated query to only show values from @:

           

           

          SELECT DISTINCT VV.ValueCache

          FROM Documents D

          INNER JOIN VariableValue VV ON D.DocumentID = VV.DocumentID

          INNER JOIN Variable VR ON VV.VariableID = VR.VariableID

          WHERE (VR.VariableName LIKE 'CurRevECO') 

               AND D.LatestRevisionNo = VV.RevisionNo

               AND VV.ConfigurationID=4

          Order by  VV.ValueCache DESC

            • Re: List from SQL Database that looks at all configurations / sheets
              Iftach Priel

              Thank you Jason,

              It doesn't work for some reason. No error message, but it just brings back zero values.

              When I delete the "AND VV.ConfigurationID=4" line, It works, but just like before, with results from "Sheet1" tab in my drawings, without the results from @ tab.

              Forgot to mention we're using ePDM 2013 if it makes a difference.

              Also, the variable in the datacard is set to "update all configurations", but since it's drawings, sheets don't act exactly like configurations. Is there a way to collect values from all sheets, as oppose to all configurations?

              Thanks...

               

               

            • Re: List from SQL Database that looks at all configurations / sheets
              Iftach Priel

              OK, I got it. Now it works.

              The problem wasn't just that the query returned values from wrong configurations, but that in returned history values as well, and not just the latest version values.

              The support team of the local SW distributor helped me with the code:

               

              SELECT DISTINCT ValueCache

              FROM VariableValue VV

              INNER JOIN

              (SELECT DocumentID, ConfigurationID, VariableID, MAX(RevisionNo) AS RevMax

              FROM VariableValue

              WHERE VariableID=(SELECT VariableID FROM Variable

               

              -- replace the variable name below with the variable you need

              WHERE VariableName='CurRevECO' and ConfigurationID='2')

               

              GROUP BY DocumentID,ConfigurationID,VariableID) T

              ON VV.DocumentID=T.DocumentID

              AND VV.ConfigurationID=T.ConfigurationID

              AND VV.VariableID=T.VariableID

              AND VV.RevisionNo=T.RevMax

              -- sort alphabetically

              ORDER BY ValueCache