10 Replies Latest reply on Aug 31, 2018 11:40 AM by Rafael Tangari

    Queying SQL Database For Return All Parts In A Vault

    Rafael Tangari

      Dear all friends,

       

      Firstly, thanks for the attention! I would like to query my Enterprise PDM database and return a table with all the parts that I have inside a specific vault in my server.

       

      Also, I would like to include in this query only the latest versions of the parts. With the columns, I want to include four metadata (Number, Description, Specification and Material).

       

      After that, I would like to query for list only the assemblies in my vault.

       

      I will use this report to populate our internal ERP/MRP system.

       

      Thanks a lot for the help!

       

      Rafael.

        • Re: Queying SQL Database For Return All Parts In A Vault
          Steven Dod

          In the PDM admin tool set your search columns to the values you want to show in the search results.  Using the PDM Search Tool, type sldprt in the name to get all parts.  Hit the "open search result" (excel looking icon) to open a CSV in Excel.  Change sldprt to sldasm to get the assemblies.

            • Re: Queying SQL Database For Return All Parts In A Vault
              Rafael Tangari

              Thank you Steven! Thanks for your help! It worked!!!

               

              Taking opportunity in this forum, is it possible to query the PDM database to return a specific bill of material by specifying an assembly name (Part number)? I searched for CTE (Common Table Expressions) in google and I found several examples, but I would like to use this query in our ERP system so we can create an external report, instead using PDM tool. With this query string, I would like to insert the code in the ERP's software.

               

              Thanks!

                • Re: Queying SQL Database For Return All Parts In A Vault
                  Joy Garon

                  Hi Rafael -

                   

                  This will get you started... I took the base from an existing report...

                  At some point you are going to need to get familiar where things are in the database (tables and how they are connected).

                   

                  /* This query lists the tree structure of an assembly */

                  Declare @XrefTable Table( iIndex Int, ParentID Int, DocumentID Int )

                    Declare @iIndex Int

                    Declare @iRootID Int

                    Declare @fName nvarchar(max)

                   

                    Set @iIndex = 0

                    Set @fName = 'full_crank-assy.sldasm' -- enter name of your assembly here

                    Set @iRootID = (Select Documents.DocumentID from documents where Documents.Filename = @fname)

                   

                    Insert Into @XrefTable( iIndex, ParentID, DocumentID )

                    Select Distinct  @iIndex,  0, @iRootID

                    From Documents Doc

                   

                    While( @@RowCount > 0 )

                    Begin

                       Set @iIndex = @iIndex + 1

                       Insert Into @XrefTable( iIndex, ParentID, DocumentID )

                       Select @iIndex, X.DocumentID, X.XRefDocument

                       From @XrefTable r, XRefs X

                       Where

                          r.iIndex = (@iIndex - 1) And

                          X.DocumentID = r.DocumentID And

                          X.XrType <> 2 --do not process circular references

                    End

                   

                    Select iIndex as 'Level',

                           IsNull( (Select Filename From Documents Where DocumentID = X.ParentID), '') as 'Parent Name',

                           (Select Filename From Documents Where DocumentID = X.DocumentID) as 'Part'

                    From @XrefTable X

                   

                   

                  Cheers,

                  Joy