6 Replies Latest reply on Feb 12, 2013 9:40 AM by Shawn Kelly

    SQL Question from a SQL Noob...

    Brian Dalton

      I'm getting my feet wet on using SQL to query the PDM database, and I need to get a list of files and several of the variables attached to them.  My code works fine on those files where each of the required variables actually exists, but there are a number of older files which were created before some of these variables were implemented, so these older files have no value at all for particular variables.


      I'd like them to show up but with that space just left empty, instead the records with missing variables just don't get included in the query results.  I'm a ham-fisted noob on this stuff, so please don't laugh (well, OK. Go ahead) at my clumsy code.  Could someone explain to me how to fix it so I can still get records where some of the fields have no values?


      Here goes...


      SELECT XD.Filename AS 'Component', VVRev.ValueText AS 'Rev', VVDesc.ValueText AS 'Description',

         X.RefCount AS 'Qty', VVVend.ValueText AS 'Vendor', VVVNum.ValueText AS 'Vendor P/N'

      FROM XRefs X, Documents D, Documents XD, VariableValue VVRev, VariableValue VVDesc,

         VariableValue VVVend, VariableValue VVVNum

      WHERE D.DocumentID = 76503 AND

            X.DocumentID = D.DocumentID AND

            X.RevNr = D.LatestRevisionNo AND

            XD.DocumentID = X.XRefDocument AND

            VVRev.DocumentID = XD.DocumentID AND

            VVRev.VariableID = 46 AND --Revision

            VVRev.RevisionNo = (SELECT MAX(VVR1.RevisionNo)

                                FROM VariableValue VVR1

                                WHERE VVR1.DocumentID = XD.DocumentID AND

                                      VVR1.VariableID = VVRev.VariableID) AND                         

            VVRev.ConfigurationID = (SELECT MAX(VVR2.ConfigurationID)

                                     FROM VariableValue VVR2

                                     WHERE VVR2.DocumentID = XD.DocumentID AND

                                           VVR2.VariableID = VVRev.VariableID AND

                                           VVR2.RevisionNo = VVRev.RevisionNo) AND

            VVDesc.DocumentID = XD.DocumentID AND

            VVDesc.VariableID = 45 AND --Description

            VVDesc.RevisionNo = (SELECT MAX(VVD1.RevisionNo)

                                 FROM VariableValue VVD1

                                 WHERE VVD1.DocumentID = XD.DocumentID AND

                                       VVD1.VariableID = VVDesc.VariableID) AND

            VVDesc.ConfigurationID = (SELECT MAX(VVD2.ConfigurationID)

                                     FROM VariableValue VVD2

                                     WHERE VVD2.DocumentID = XD.DocumentID AND

                                           VVD2.VariableID = VVDesc.VariableID AND

                                           VVD2.RevisionNo = VVDesc.RevisionNo) AND

            VVVend.DocumentID = XD.DocumentID AND

            VVVend.VariableID = 49 AND --Vendor

            VVVend.RevisionNo = (SELECT MAX(VVV1.RevisionNo)

                                 FROM VariableValue VVV1

                                 WHERE VVV1.DocumentID = XD.DocumentID AND

                                       VVV1.VariableID = VVVend.VariableID) AND

            VVVend.ConfigurationID = (SELECT MAX(VVV2.ConfigurationID)

                                     FROM VariableValue VVV2

                                     WHERE VVV2.DocumentID = XD.DocumentID AND

                                           VVV2.VariableID = VVVend.VariableID AND

                                           VVV2.RevisionNo = VVVend.RevisionNo) AND                                    

            VVVNum.DocumentID = XD.DocumentID AND

            VVVNum.VariableID = 56 AND --Vendor P/N

            VVVNum.RevisionNo = (SELECT MAX(VVVN1.RevisionNo)

                                 FROM VariableValue VVVN1

                                 WHERE VVVN1.DocumentID = XD.DocumentID AND

                                       VVVN1.VariableID = VVVNum.VariableID) AND

            VVVNum.ConfigurationID = (SELECT MAX(VVVN2.ConfigurationID)

                                     FROM VariableValue VVVN2

                                     WHERE VVVN2.DocumentID = XD.DocumentID AND

                                           VVVN2.VariableID = VVVNum.VariableID AND

                                           VVVN2.RevisionNo = VVVNum.RevisionNo)


      See, I told you I was a noob 

        • Re: SQL Question from a SQL Noob...
          Tom Helsley

          Not bad for a SQL noob!  That's far better than I could do .  I thought there was a tool that let you 'visually' build queries like you can in MS Access, but I can't for the life of me remember where I saw it.  Was it in Management Studio or something else?  Oh, back to your question...


          From one SQL noob to another, I think you can use a keyword like "Nothing" or "Null", or maybe just two double-quotes ("").  I could be totally wrong, but I'm also interested in the answer.



            • Re: SQL Question from a SQL Noob...
              Ravi Teja

              Hi tom,

                      Are you talking about "Query editor" in SQL.




              this is really helpful in writing simple selet queries.can be used in getting doc ID for below query but i couldnt use is to write query where pivoting or later version kind of stuff is involved.I wish this tool was well documented.





              Ravi T

            • Re: SQL Question from a SQL Noob...
              Ravi Teja

              Hi Brian,

                     Take a look at this query,


              --Dropping Temp tables after previous use

              IF OBJECT_ID('tempdb..#Forumtemp1') IS NOT NULL

                        DROP TABLE #Forumtemp1



              IF OBJECT_ID('tempdb..#Forumtemp2') IS NOT NULL

                        DROP TABLE #Forumtemp2



              IF OBJECT_ID('tempdb..#Forumtemp3') IS NOT NULL

                        DROP TABLE #Forumtemp3



              IF OBJECT_ID('tempdb..#Forumtemp4') IS NOT NULL

                        DROP TABLE #Forumtemp4







              CREATE TABLE #Forumtemp1 (VarID INT)



              INSERT INTO #Forumtemp1

              SELECT VariableID

              FROM Variable

              --********************************Variable Name here****************************************

              WHERE VariableName IN (








              --Enter Required Variable in above Brackets

              --Query to Create and Fill a Table with Variable Values by considering above variableIDs

              CREATE TABLE #Forumtemp2 (

                        DocID INT

                        ,ConfigurationName NVARCHAR(255)

                        ,VariableName NVARCHAR(255)

                        ,ValueText NVARCHAR(Max)




              INSERT INTO #Forumtemp2 (






              SELECT DocumentID




              FROM VariableValue VV

              INNER JOIN (

                        SELECT DocumentID AS DocID

                                  ,ConfigurationID AS ConfID

                                  ,VariableID AS VarID

                                  ,MAX(RevisionNo) AS RevMax

                        FROM VariableValue

                        JOIN #Forumtemp1 ON #Forumtemp1.VarID = VariableValue.VariableID

                        GROUP BY DocumentID



                        ) T ON VV.DocumentID = T.DocID

                        AND VV.ConfigurationID = T.ConfID

                        AND VV.VariableID = T.VarID

                        AND VV.RevisionNo = T.RevMax

              INNER JOIN DocumentConfiguration ON DocumentConfiguration.ConfigurationID = T.ConfID

              INNER JOIN Variable ON Variable.VariableID = T.VarID



              --Above Table will have lots of results not filtered according to filename and location

              ----Query to Create and Fill a Table with Variable Values taken from above table and filtered according to documentID taken from the query on TOP

              CREATE TABLE #Forumtemp3 (

                        DocID INT

                        ,ConfigurationName NVARCHAR(255)

                        ,VariableName NVARCHAR(255)

                        ,ValueText NVARCHAR(Max)




              INSERT INTO #Forumtemp3 (






              SELECT *

              FROM #Forumtemp2

              --Exclude Folders

              WHERE DocID != '1'


                        --Following query will get all the Document IDs

                        DocID IN (

                                  SELECT Documents.DocumentID

                                  FROM Documents

                                  INNER JOIN DocumentsInProjects ON Documents.DocumentID = DocumentsInProjects.DocumentID

                                  INNER JOIN Projects ON DocumentsInProjects.ProjectID = Projects.ProjectID

                                  WHERE (Documents.Filename LIKE 'Filename sample')

                                  -- For File name sample use '%' as wild Card Under Score '_' for wild card for 1 character.




              --select * from #Forumtemp3

              -- Selecting and Pivoting

              --********************************Variable Name here****************************************

              CREATE TABLE #Forumtemp4 (

                        path VARCHAR(MAX)

                        ,FileName VARCHAR(MAX)

                        ,ConfigurationName VARCHAR(MAX)

                        ,Variable1 VARCHAR(20)

                        ,Variable2 VARCHAR(20)

                        ,Variable3 VARCHAR(MAX)

                        ,Variable4 VARCHAR(MAX)




              --********************************Variable Name here****************************************

              INSERT INTO #Forumtemp4 (









              --********************************Variable Name here****************************************

              SELECT path







              FROM (

                        SELECT Path





                        FROM Documents

                        INNER JOIN (

                                  SELECT DocumentsInProjects.DocumentID

                                            ,Projects.Path AS Path

                                  FROM DocumentsInProjects

                                  LEFT JOIN Projects ON Projects.ProjectID = DocumentsInProjects.ProjectID

                                  ) AS P ON P.DocumentID = Documents.DocumentID

                        INNER JOIN #Forumtemp3 T ON T.DocID = Documents.DocumentID

                        WHERE Path LIKE '%\Type Folder Name Here\%'

                        ) AS SourceTable

              PIVOT(MAX(ValueText) FOR VariableName IN

                                  --********************************Variable Name here****************************************







                                            )) AS PivotTable

              ORDER BY [Variable1]










              select * from #Forumtemp4



              --Dropping all the temp tables created in the query

              DROP TABLE #Forumtemp1



              DROP TABLE #Forumtemp2



              DROP TABLE #Forumtemp3



              DROP TABLE #Forumtemp4






              I will execute your query as soon as i find a machine with SQL on it...:D

              I dont see a need for using Xref table (if the ).


              "D.DocumentID = 76503 "



              are you trying to get result for one file at a time?


              One more thing


              If a File has five variables filled at Version 1

              and only one of them is changes at version 2 then Vv table will have only 6 entries


              5 entries for verison 1 and

              1 entry for version 2


              If you have considered this in your query then Ignore this.







              Ravi T

                • Re: SQL Question from a SQL Noob...
                  Brian Dalton

                  OUCH!  That makes my brain hurt! Does it really need to be that complicated?  I can't understand 90% of what you're doing there... too much of a noob


                  My intent would be to get the info for one file at a time, but for testing purposes I hard-coded the DocumentID in.  Eventually it would be programmatically set as necessary.




                  Let's back up a bit. The following code works fine, and returns the exact set of rows I'm looking for:


                  SELECT XD.Filename AS 'Component', X.RefCount AS 'Qty'

                  FROM XRefs X, Documents D, Documents XD

                  WHERE D.DocumentID = 76503 AND

                        X.DocumentID = D.DocumentID AND

                        X.RevNr = D.LatestRevisionNo AND

                        XD.DocumentID = X.XRefDocument


                  My purpose is to take an assembly file (in this case DocumentID 76503) and list all of the files it references, as a BOM-type list.  This query gives the filename and the quantity used, which are both available in the Documents table and the XRefs table.


                  What I need, however, is to also include variable values that are associated with each of the returned rows, such as Revision, Description, Vendor and Vendor P/N.  These value are of course stored in the VariableValue table, linked to by VariableID and DocumentID.


                  The problems to be solved in getting a variable value are:

                  1. I must specify which version (called 'Revision' in the DB, unfortunately) of the variable value I want.  I need to determine the latest version that exists
                  2. I must specify which configuration I want to read the variable value from.  For now, I'm just going for the highest ConfigurationID that exists.
                  3. THIS IS THE PROBLEM PART --> I must include rows for which no such variable value exists.  If no value has ever been written to the variable for that file, as I understand it the value will be NULL, but I'm not sure, since there simply won't be a corresponding record in the VariableValue table.


                  So if I include a part that says WHERE XD.DocumentID = VV.DocumentID AND VV.VariableID = 46, there will be no result if no value has ever been written to variable 46.  I want the row (as defined in the working query above) to be displayed, but with the field for variable 46 to simply be blank.  Every attempt I make causes the row to be excluded because it doesn't have a value for variable 46.


                  Some parts are made in house so they simply don't have a value for Vendor or Vendor P/N.  These are the parts that are kicking my butt.

                    • Re: SQL Question from a SQL Noob...
                      Joy Garon

                      Brian -


                      The query will be more complex - the issue is that you need to recurse the variablevalue table in reverse for each variable until a) you find a value or b) you recurse all versions and there was no value (hence it's empty).


                      For example, let's say you had a file named ABC.sldprt and there are 10 versions of the file.

                      You want to retrieve 4 properties (Number, Description, Material and Drawn By.




                      Starting with the MAX(Revisions.RevNr) for each document and for each variable you need to recurse (from high to low) each variable until you get a value and save the value once you get it.

                      I the above example, you would find the Number varable value in version 9, Desciption in version 7 and etc.



                  • Re: SQL Question from a SQL Noob...
                    Shawn Kelly

                    You could try something like this.  It's a general xref list but utilizes a handy user-defined function in the EPDM database.




                    SELECT DISTINCT D.DocumentID, D.Filename, XRD.Filename AS RefFile, XRCfg.DocumentID AS XrefDocID, MAX(XRCfg.RevisionNo) AS MaxVer, XR.RefCount,

                    (SELECT     dbo.acFindAnyVariableValueBOM(N'Revision', XRCfg.DocumentID, MAX(XRCfg.RevisionNo)) AS Revision) AS Revision,

                    (SELECT     dbo.acFindAnyVariableValueBOM(N'Description', XRCfg.DocumentID, MAX(XRCfg.RevisionNo)) AS Description) AS Description

                    FROM         Documents AS D INNER JOIN

                                          XRefs AS XR ON D.DocumentID = XR.DocumentID INNER JOIN

                                          DocumentRevisionConfiguration AS XRCfg ON XR.XRefDocument = XRCfg.DocumentID AND XR.XRefRevNr = XRCfg.RevisionNo INNER JOIN

                                          Documents AS XRD ON XR.XRefDocument = XRD.DocumentID

                    GROUP BY D.DocumentID, D.Filename, XRD.Filename, XR.RefCount, XRCfg.DocumentID

                    HAVING      (D.DocumentID = 76503)


                    The user-defined function needs the variable by name, documentID (which in your case is the xrefID and the version.  In the example I am using the max version.



                    SELECT dbo.acFindAnyVariableValueBOM (

                       <@acVariableName, nvarchar(256),>

                      ,<@iDocumentID, int,>

                      ,<@iRevNr, int,>)