31 Replies Latest reply on Jan 13, 2017 5:55 PM by Tara Campese

    SQL query for multiple epdm variable values in a file

    Tara Campese

      I created a View that makes a table showing a document's file name, revision variable, document ID, project ID, cleaning variable, revision number, and part number variable.  However, there seems to be a row for each value of these variables.

      Here's my View:

      SELECT     dbo.Documents.Filename, VariableValue_1.ValueText AS [DRW Rev], dbo.Documents.DocumentID, dbo.DocumentsInProjects.ProjectID,

                            VariableValue_2.ValueText AS [Cleaning Required], VariableValue_1.RevisionNo, dbo.VariableValue.ValueText AS [Part Number]

      FROM         dbo.Documents INNER JOIN

                            dbo.VariableValue ON dbo.Documents.DocumentID = dbo.VariableValue.DocumentID INNER JOIN

                            dbo.Variable ON dbo.VariableValue.VariableID = dbo.Variable.VariableID INNER JOIN

                            dbo.VariableValue AS VariableValue_1 ON dbo.Documents.DocumentID = VariableValue_1.DocumentID INNER JOIN

                            dbo.Variable AS Variable_1 ON VariableValue_1.VariableID = Variable_1.VariableID INNER JOIN

                            dbo.VariableValue AS VariableValue_2 ON dbo.Documents.DocumentID = VariableValue_2.DocumentID INNER JOIN

                            dbo.Variable AS Variable_2 ON VariableValue_2.VariableID = Variable_2.VariableID INNER JOIN

                            dbo.DocumentsInProjects ON dbo.Documents.DocumentID = dbo.DocumentsInProjects.DocumentID

      WHERE     (dbo.Documents.Filename LIKE '%.slddrw') AND (dbo.Variable.VariableName LIKE 'Part Number') AND (dbo.VariableValue.ValueText LIKE 'RBT-%') AND

                            (Variable_1.VariableName LIKE 'Revision') AND (Variable_2.VariableName LIKE 'Cleaning Required') AND (dbo.Documents.Deleted = 0) AND

                            (dbo.Documents.Shared <> 0) AND (dbo.Documents.DocTypeID = 1) AND (NOT (VariableValue_1.ValueText LIKE '')) AND (NOT (VariableValue_2.ValueText LIKE '')) AND

                            (NOT (dbo.VariableValue.ValueText LIKE ''))

      Here is the query that uses it:

      select * from dbo.drawingbypartno

      where [Part Number] like '%-00B876%'

      order by RevisionNo desc

      Here are my results:

       

      What I want to have is something that shows one line for each RevisionNo and it should be tied to the corresponding values for the file that is at that revision. In the image above, the correct line for RevionNo 12 is line 5. Looking into SQL commands, I think I need to use the UNION command, but I can't quite figure out how to format it.

       

      Any help would be appreciated.

       

      Thank you,

      Tara

        • Re: SQL query for multiple epdm variable values in a file
          Tim Webb

          Hi Tara,

           

          You need to pivot the values. Check out the query listed in S-059164

          https://customerportal.solidworks.com/eservice_enu/start.swe?SWECmd=Login&SWEPL=1&SRN=&SWETS=1267144948839

          Hope this helps,

          Tim

          • Re: SQL query for multiple epdm variable values in a file
            Steve Ostrovsky

            Here's a little sample code I use to get the MAX RevisionNo value of from the VariableValue table:

             

            SELECT vv.VariableID, vv.DocumentID , vv.ProjectID, vv.RevisionNo, vv.ValueText

            FROM VariableValue vv

            Inner join (

                            Select VariableID, max(RevisionNo) as maxrev

                            FROM VariableValue

                            GROUP BY VariableID

            ) mr on vv.VariableID = mr.VariableID and vv.RevisionNo = mr.maxrev

            inner join documents d on d.documentid=vv.documentid

            where d.documentid = '1042'

             

            You can see the difference it provides in the screen shot below.

             

            • Re: SQL query for multiple epdm variable values in a file
              Charley Saint

              Try this guy, it only uses the latest versions of variables:

              with maxvar as (select documentid, variableid, configurationid, max(revisionno) revisionno from variablevalue group by documentid, variableid, configurationid)
              
              
              select filename, [Revision], [Cleaning Required], [Part Number]
              from
              (select filename, v.variablename, vv.valuetext
              from
              documents d
              inner join DocumentsInProjects dp on d.DocumentID = dp.DocumentID
              inner join projects p on p.ProjectID = dp.ProjectID
              inner join variablevalue vv on vv.DocumentID = d.DocumentID
              inner join maxvar mv on mv.DocumentID = vv.DocumentID and mv.ConfigurationID = vv.ConfigurationID
                and mv.VariableID = vv.VariableID and mv.revisionno = vv.RevisionNo
              inner join Variable v on v.VariableID = vv.VariableID
              inner join DocumentConfiguration dc on dc.ConfigurationID = vv.ConfigurationID
              where filename LIKE '%.slddrw' and d.Deleted = 0 and dc.ConfigurationName = '@'
              ) a
              PIVOT
              (MAX(ValueText)
              for variablename in ([Revision], [Cleaning Required], [Part Number])
              )pivot_table
              
                • Re: SQL query for multiple epdm variable values in a file
                  Tara Campese

                  So this works if I'm searching by file name, but I absolutely have to search by part number for my application. When I try to search by part number, it yields no results.

                   

                  To do this, I added "where [Part Number] like '%-876%'" to the bottom of your query.  The results should have been for DRW-20170103-6879.

                   

                  Am I doing something wrong? I really don't know much about SQL.

                    • Re: SQL query for multiple epdm variable values in a file
                      Charley Saint

                      No that should be right, maybe try the query without the "and dc.ConfigurationName = '@' " and see if it comes back, though it's a drawing so I assume the variables are associated to the @ tab...

                        • Re: SQL query for multiple epdm variable values in a file
                          Charley Saint

                          wait nevermind, '%-876%' wouldn't come back with anything because there are characters between the dash and 876, you need the original '%-00B876%'

                            • Re: SQL query for multiple epdm variable values in a file
                              Tara Campese

                              Yeah, I tried that too since the Drawing File variable values are located in the @ config. But that didn't change the results.

                               

                              Edit: I didn't see your second response. You're totally right! My bad. Thank you so much for your help!

                               

                              Now there is only 1 more issue. In the latest version, one of the values is NULL. I need to get the latest non-null value. Steve's response got the right non-null values, but your code got the correct file. Almost there...

                                • Re: SQL query for multiple epdm variable values in a file
                                  Charley Saint

                                  Can you share the results when you run with where filename like 'DRW-20170103-6879%' instead of part number

                                    • Re: SQL query for multiple epdm variable values in a file
                                      Charley Saint

                                      Gotcha, missed where you changed to DRW Rev later on, you can replace Revision in the select statement and in the pivot statement and that should do it for you

                                        • Re: SQL query for multiple epdm variable values in a file
                                          Tara Campese

                                          I'm not sure what you mean. Replace it with what? I already changed it to [DRW Rev] but I'm still getting the same results. Sorry for all the questions.

                                            • Re: SQL query for multiple epdm variable values in a file
                                              Charley Saint

                                              No worries, I'm getting all mixed up haha, try this version:

                                               

                                              with maxvar as (select documentid, variableid, configurationid, max(revisionno) revisionno from variablevalue group by documentid, variableid, configurationid)
                                              
                                              
                                              select filename, [Revision] as [DRW Rev], [Cleaning Required], [Part Number]
                                              from
                                              (select filename, v.variablename, vv.valuetext
                                              from
                                              documents d
                                              inner join DocumentsInProjects dp on d.DocumentID = dp.DocumentID
                                              inner join projects p on p.ProjectID = dp.ProjectID
                                              inner join variablevalue vv on vv.DocumentID = d.DocumentID
                                              inner join maxvar mv on mv.DocumentID = vv.DocumentID and mv.ConfigurationID = vv.ConfigurationID
                                                and mv.VariableID = vv.VariableID and mv.revisionno = vv.RevisionNo
                                              inner join Variable v on v.VariableID = vv.VariableID
                                              inner join DocumentConfiguration dc on dc.ConfigurationID = vv.ConfigurationID
                                              where filename LIKE '%.slddrw' and d.Deleted = 0 and dc.ConfigurationName = '@'
                                              ) a
                                              PIVOT
                                              (MAX(ValueText)
                                              for variablename in ([Revision], [Cleaning Required], [Part Number])
                                              )pivot_table
                                              where [Part Number] like '%-876%'
                                              
                                                • Re: SQL query for multiple epdm variable values in a file
                                                  Charley Saint

                                                  Sorry, be sure to fix the part number in that query again :/

                                                    • Re: SQL query for multiple epdm variable values in a file
                                                      Tara Campese

                                                      Perfect! Thank you so much! I think I can follow what you did so I can do this myself next time. Pivot tables are totally new to me.

                                                       

                                                      Is there any way to make the query run faster? Is that just a result of all the pivot tables?

                                                        • Re: SQL query for multiple epdm variable values in a file
                                                          Charley Saint

                                                          This is a much faster version but way harder to understand, basically it filters on part number before applying the pivot so it's working with much less data:

                                                           

                                                          with maxvar as (select documentid, variableid, configurationid, max(revisionno) revisionno from variablevalue group by documentid, variableid, configurationid)
                                                          
                                                          
                                                          select filename, [Revision] as [DRW Rev], [Cleaning Required], [Part Number]
                                                          from
                                                          (select filename, v.variablename, vv.valuetext
                                                          from
                                                          documents d
                                                          inner join DocumentsInProjects dp on d.DocumentID = dp.DocumentID
                                                          inner join projects p on p.ProjectID = dp.ProjectID
                                                          inner join variablevalue vv on vv.DocumentID = d.DocumentID
                                                          inner join maxvar mv on mv.DocumentID = vv.DocumentID and mv.ConfigurationID = vv.ConfigurationID
                                                            and mv.VariableID = vv.VariableID and mv.revisionno = vv.RevisionNo
                                                          inner join Variable v on v.VariableID = vv.VariableID
                                                          inner join DocumentConfiguration dc on dc.ConfigurationID = vv.ConfigurationID
                                                          inner join FileExtension fe on fe.ExtensionID = d.ExtensionID
                                                          where fe.Extension = 'slddrw' and d.Deleted = 0 and dc.ConfigurationName = '@'
                                                          and exists (select 1 from 
                                                          documents d1
                                                          inner join DocumentsInProjects dp1 on d1.DocumentID = dp1.DocumentID
                                                          inner join projects p1 on p1.ProjectID = dp1.ProjectID
                                                          inner join variablevalue vv1 on vv1.DocumentID = d1.DocumentID
                                                          inner join maxvar mv1 on mv1.DocumentID = vv1.DocumentID and mv1.ConfigurationID = vv1.ConfigurationID
                                                            and mv1.VariableID = vv1.VariableID and mv1.revisionno = vv1.RevisionNo
                                                          inner join Variable v1 on v1.VariableID = vv1.VariableID
                                                          where d1.DocumentID = d.DocumentID and v.VariableName = 'Part Number' and vv.ValueText like '%-00B876%'
                                                          )
                                                          ) a
                                                          PIVOT
                                                          (MAX(ValueText)
                                                          for variablename in ([Revision], [Cleaning Required], [Part Number])
                                                          )pivot_table
                                                          
                                                            • Re: SQL query for multiple epdm variable values in a file
                                                              Tara Campese

                                                              Thank you! This is great for my reference. Unfortunately, I can't use it for my query as part number is what is given in the query to filter out the information.

                                                               

                                                              For reference, I wrote an add-in (with Tim Webb's assistance) that takes the BOM of an assembly and generates a work order. For the work order, it lists the corresponding drawing. The easiest way to do this was to tie the part and the drawing together using a unique part number (only found in 1 configuration and on 1 drawing). Then it also generates a conisio link to the part and drawing to place in the work order. I was running an epdm search on the part number for each part to get the information, but that was incredibly slow. To speed it up, I have it send a query to SQL with the variable Part Number as the search criteria.

                                                               

                                                              This is the result:

                                                              I had a SQL query running before that was faster, but not nearly as robust. We were getting the wrong drawing name and hyperlink if the user EVER had accidentally checked the drawing in with the wrong configuration or part inside of it. 

                                                                • Re: SQL query for multiple epdm variable values in a file
                                                                  Charley Saint

                                                                  Ah gotcha, it'd probably take some work to shoehorn that into the existing code then. This version doesn't use a pivot and should take care of the issue you were seeing:

                                                                   

                                                                  with maxvar as (select documentid, variableid, configurationid, max(revisionno) revisionno 
                                                                  from variablevalue 
                                                                  group by documentid, variableid, configurationid)
                                                                  
                                                                  
                                                                  select filename, vv1.ValueText as [DRW Rev], vv2.ValueText as [Cleaning Required], vv3.ValueText as [Part Number]
                                                                  from
                                                                  documents d
                                                                  inner join DocumentsInProjects dp on d.DocumentID = dp.DocumentID
                                                                  inner join projects p on p.ProjectID = dp.ProjectID
                                                                  left outer join 
                                                                  (select vv1.documentid, vv1.ConfigurationID, vv1.ValueText from variablevalue vv1 
                                                                  inner join maxvar mv1 on mv1.DocumentID = vv1.DocumentID and mv1.ConfigurationID = vv1.ConfigurationID
                                                                    and mv1.VariableID = vv1.VariableID and mv1.revisionno = vv1.RevisionNo
                                                                  inner join Variable v1 on v1.VariableID = vv1.VariableID and v1.variablename = 'Revision') vv1 on vv1.DocumentID = d.DocumentID
                                                                  left outer join 
                                                                  (select vv2.documentid, vv2.ConfigurationID, vv2.ValueText from variablevalue vv2
                                                                  inner join maxvar mv2 on mv2.DocumentID = vv2.DocumentID and mv2.ConfigurationID = vv2.ConfigurationID
                                                                    and mv2.VariableID = vv2.VariableID and mv2.revisionno = vv2.RevisionNo
                                                                  inner join Variable v2 on v2.VariableID = vv2.VariableID and v2.variablename = 'Cleaning Required') vv2 on vv2.DocumentID = d.DocumentID
                                                                  left outer join 
                                                                  (select vv3.documentid, vv3.ConfigurationID, vv3.ValueText from variablevalue vv3
                                                                  inner join maxvar mv3 on mv3.DocumentID = vv3.DocumentID and mv3.ConfigurationID = vv3.ConfigurationID
                                                                    and mv3.VariableID = vv3.VariableID and mv3.revisionno = vv3.RevisionNo
                                                                  inner join Variable v3 on v3.VariableID = vv3.VariableID and v3.variablename = 'Part Number') vv3 on vv3.DocumentID = d.DocumentID
                                                                  inner join DocumentConfiguration dc on dc.ConfigurationID = vv1.ConfigurationID 
                                                                    and dc.ConfigurationID = vv2.ConfigurationID and dc.ConfigurationID = vv3.ConfigurationID
                                                                  inner join FileExtension fe on fe.ExtensionID = d.ExtensionID
                                                                  where fe.Extension = 'slddrw' and d.Deleted = 0 and dc.ConfigurationName = '@'
                                                                  
                                                                  
                                                                  
                                                                  
                                                          • Re: SQL query for multiple epdm variable values in a file
                                                            Tara Campese

                                                            One more thing (sorry). I can't figure out how to add the columns documentid and projectid back into this. I need those two column results.

                                                             

                                                            Nevermind. I got it!  Thanks again!!!

                                                      • Re: SQL query for multiple epdm variable values in a file
                                                        Tara Campese

                                                        I get the same results when I search by part number. DRW Rev is null right now because the file was checked out after transitioning so the variable stamped in epdm is null, but the variable text on the data card is "B". I think it's just pulling the variable value from epdm rather than the text in the file?

                                                         

                                                        Edit: just saw your response. you can ignore this.