14 Replies Latest reply on Jan 23, 2017 2:13 PM by Sebastian Soyke

    Is there a sql query, that list all files, which belong to selected EPDM categorise?

    Sebastian Soyke
    Sebastian Soyke May 12, 2015 5:18 PM

      Is there a SQL query, that list all files, which belong to selected EPDM categorise?

       

      i would like to list all documents that belong to 3 different categories. Also with The documents, to list workflow state, and some variables such description, doc number, project no, Etc.

       

      i don't have any experience with SQL.

       

      Cheers.

      • 4617 Views
      • Categories: Enterprise PDM
      • Tags:
        • Re: Is there a sql query, that list all files, which belong to selected EPDM categorise?
          Charley Saint
          Charley Saint May 12, 2015 6:05 PM (in response to Sebastian Soyke)

          Hey Sebastian,

           

          Took what I posted earlier and added to it, you'll have to copy/paste lines 7-9 for each variable you want to add, and add the variable to the select list in line 5

           

          with maxvars as   
(select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid   
from VariableValue group by DocumentID, ConfigurationID, VariableID)  
  
select d.filename, dt.TypeName, vv.ValueText as [Description] from Documents d   
inner join VariableValue vv on vv.DocumentID = d.DocumentID  
inner join maxvars mv on mv.DocumentID = vv.DocumentID and mv.ConfigurationID = vv.ConfigurationID  
and mv.RevisionNo = vv.RevisionNo and mv.VariableID = vv.VariableID  
inner join variable v on v.variableid = vv.variableid and v.VariableName = 'Description'
inner join DocType dt on d.DocTypeID = dt.DocTypeID
inner join status s on s.statusid = d.currentstatusid
where d.deleted = 0
            • Re: Is there a sql query, that list all files, which belong to selected EPDM categorise?
              Sebastian Soyke
              Sebastian Soyke May 13, 2015 5:15 AM (in response to Charley Saint)

              Hey Charley,

               

              I thought I've got it but my code don't work. Tried different variations and have an error. below is my latest query.

               

              with maxvars as    
              (select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid    
              from VariableValue group by DocumentID, ConfigurationID, VariableID)   
               
              select va.ValueText as [Project No.], vb.ValueText as [Customer], d.filename, dt.TypeName from Documents d
              inner join VariableValue va on va.DocumentID = d.DocumentID   

               

              inner join maxvars mv on mv.DocumentID = va.DocumentID and mv.ConfigurationID = va.ConfigurationID   
              and mv.RevisionNo = va.RevisionNo and mv.VariableID = va.VariableID   
              inner join variable v on v.variableid = va.variableid and v.VariableName = 'Sales Order No' 

               

              inner join maxvars mv on mv.DocumentID = vb.DocumentID and mv.ConfigurationID = vb.ConfigurationID   
              and mv.RevisionNo = vb.RevisionNo and mv.VariableID = vb.VariableID   
              inner join variable v on v.variableid = vb.variableid and v.VariableName = 'Customer' 

               

              inner join DocType dt on d.DocTypeID = dt.DocTypeID 
              inner join status s on s.statusid = d.currentstatusid 
              where d.deleted = 0 

                • Re: Is there a sql query, that list all files, which belong to selected EPDM categorise?
                  Charley Saint
                  Charley Saint May 13, 2015 1:27 PM (in response to Sebastian Soyke)

                  Hey,

                   

                  Close, but you have to give each copy a unique name, it's failing because there are 2 mv's, etc. I also should have said line 6-9 instead of 7-9, my mistake:

                  with maxvars as     
(select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid     
from VariableValue group by DocumentID, ConfigurationID, VariableID)    
  
select va.ValueText as [Project No.], vb.ValueText as [Customer], d.filename, dt.TypeName as [Category], s.Name as [State]
from Documents d


inner join VariableValue va on va.DocumentID = d.DocumentID   
inner join maxvars mv on mv.DocumentID = va.DocumentID and mv.ConfigurationID = va.ConfigurationID    
and mv.RevisionNo = va.RevisionNo and mv.VariableID = va.VariableID    
inner join variable v on v.variableid = va.variableid and v.VariableName = 'Sales Order No' 


inner join VariableValue vb on vb.DocumentID = d.DocumentID  
inner join maxvars mv1 on mv1.DocumentID = vb.DocumentID and mv1.ConfigurationID = vb.ConfigurationID    
and mv1.RevisionNo = vb.RevisionNo and mv1.VariableID = vb.VariableID    
inner join variable v1 on v1.variableid = vb.variableid and v1.VariableName = 'Customer' 

inner join DocType dt on d.DocTypeID = dt.DocTypeID  
inner join status s on s.statusid = d.currentstatusid  
where d.deleted = 0
                    • Re: Is there a sql query, that list all files, which belong to selected EPDM categorise?
                      Sebastian Soyke
                      Sebastian Soyke Jun 3, 2015 7:44 AM (in response to Charley Saint)

                      Hey Charley,

                       

                      I noticed that when I add more than 5 columns the server is unresponsive. Is there a way around it? Because I would like the table to have around 13 columns. Also is there a way to restrict the search results to only show documents with dt.TypeName as [Category] called "Procedure" or "Certificate". At the moment I don't know how to restrict the search, so all categories of docs are listed.

                       

                      with maxvars as      

                      (select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid      

                      from VariableValue group by DocumentID, ConfigurationID, VariableID)     

                       

                      select va.ValueText as [Project No.], vb.ValueText as [Customer], vc.ValueText as [VDRL Document No.], vd.ValueText as [Document Title],

                      d.filename, dt.TypeName as [Category], s.Name as [State] 

                      from Documents d 

                       

                      inner join VariableValue va on va.DocumentID = d.DocumentID    

                      inner join maxvars mv on mv.DocumentID = va.DocumentID and mv.ConfigurationID = va.ConfigurationID     

                      and mv.RevisionNo = va.RevisionNo and mv.VariableID = va.VariableID     

                      inner join variable v on v.variableid = va.variableid and v.VariableName = 'Sales Order No' 

                       

                      inner join VariableValue vb on vb.DocumentID = d.DocumentID   

                      inner join maxvars mv1 on mv1.DocumentID = vb.DocumentID and mv1.ConfigurationID = vb.ConfigurationID     

                      and mv1.RevisionNo = vb.RevisionNo and mv1.VariableID = vb.VariableID     

                      inner join variable v1 on v1.variableid = vb.variableid and v1.VariableName = 'Customer'  

                       

                      inner join VariableValue vc on vc.DocumentID = d.DocumentID   

                      inner join maxvars mv2 on mv2.DocumentID = vc.DocumentID and mv2.ConfigurationID = vc.ConfigurationID     

                      and mv2.RevisionNo = vc.RevisionNo and mv2.VariableID = vc.VariableID     

                      inner join variable v2 on v2.variableid = vc.variableid and v2.VariableName = 'Doc Number'  

                       

                      inner join VariableValue vd on vd.DocumentID = d.DocumentID   

                      inner join maxvars mv3 on mv3.DocumentID = vd.DocumentID and mv3.ConfigurationID = vd.ConfigurationID     

                      and mv3.RevisionNo = vd.RevisionNo and mv3.VariableID = vd.VariableID     

                      inner join variable v3 on v3.variableid = vd.variableid and v3.VariableName = 'Document Description'  

                       

                      inner join DocType dt on d.DocTypeID = dt.DocTypeID   

                      inner join status s on s.statusid = d.currentstatusid   

                      where d.deleted = 0

                        • Re: Is there a sql query, that list all files, which belong to selected EPDM categorise?
                          Charley Saint
                          Charley Saint Jun 3, 2015 12:11 PM (in response to Sebastian Soyke)

                          Hey Sebastian,

                           

                          You'll have to break into a bit more complicated SQL to make it run with that many columns, namely pivot tables.

                           

                          with maxvars as      
(select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid      
from VariableValue group by DocumentID, ConfigurationID, VariableID)     


select d.filename, dt.TypeName as [Category], s.Name as [State],
[Document Description], [Doc Number], [Sales Order No], [Customer]
from Documents d  
inner join DocType dt on d.DocTypeID = dt.DocTypeID   
inner join status s on s.statusid = d.currentstatusid  
inner join
(SELECT vv.DocumentID, vv.ConfigurationID, VariableName, ValueText
    FROM VariableValue vv
    inner join Variable v on vv.VariableID = v.VariableID
    inner join maxvars mv on vv.DocumentID = mv.DocumentID and mv.ConfigurationID = vv.ConfigurationID
  and mv.VariableID = vv.VariableID and mv.RevisionNo = vv.RevisionNo
    ) AS SourceTable
    
PIVOT
(
MAX(ValueText)
FOR VariableName IN ([Document Description], [Doc Number], [Sales Order No], [Customer])
) AS PivotTable on PivotTable.DocumentID = d.DocumentID
where d.deleted = 0
and (dt.TypeName = 'Procedure' or dt.TypeName = 'Certificate')
                  • Re: Is there a sql query, that list all files, which belong to selected EPDM categorise?
                    Sebastian Soyke
                    Sebastian Soyke Jan 10, 2017 10:22 AM (in response to Sebastian Soyke)

                    Dear All,

                     

                    2 years ago Charley Saint, did build the Query for me. I have been using is since for many different variables and documents in the EPDM vault. However, I would like to add a line to look only for files in specified locations.

                    I did insert the line 25, but it does not work.

                     

                     

                    1. with maxvars as      
                    2. (select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid      
                    3. from VariableValue group by DocumentID, ConfigurationID, VariableID)     
                    6. select d.filename, dt.TypeName as [Category], s.Name as [State],
                    7. [Document Description], [Doc Number], [Sales Order No], [Customer]
                    8. from Documents d  
                    9. inner join DocType dt on d.DocTypeID = dt.DocTypeID   
                    10. inner join status s on s.statusid = d.currentstatusid  
                    11. inner join
                    12. (SELECT vv.DocumentID, vv.ConfigurationID, VariableName, ValueText
                    13.     FROM VariableValue vv
                    14.     inner join Variable v on vv.VariableID = v.VariableID
                    15.     inner join maxvars mv on vv.DocumentID = mv.DocumentID and mv.ConfigurationID = vv.ConfigurationID
                    16.   and mv.VariableID = vv.VariableID and mv.RevisionNo = vv.RevisionNo
                    17.     ) AS SourceTable
                    18.     
                    19. PIVOT
                    20. (
                    21. MAX(ValueText)
                    22. FOR VariableName IN ([Document Description], [Doc Number], [Sales Order No], [Customer])
                    23. ) AS PivotTable on PivotTable.DocumentID = d.DocumentID
                    24. where d.deleted = 0
                    25. and d.filename= (SELECT d.filename FROM Documents d WHERE Path '\EPDMVault\Projects\Current\')
                    26. and (dt.TypeName = 'Procedure' or dt.TypeName = 'Certificate')
                      • Re: Is there a sql query, that list all files, which belong to selected EPDM categorise?
                        Charley Saint
                        Charley Saint Jan 13, 2017 1:40 PM (in response to Sebastian Soyke)

                        Hey Sebastian,

                         

                        So close, just move it above the pivot:

                         

                        --edit, had to move a few other things around

                         

                        with maxvars as      
(select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid      
from VariableValue group by DocumentID, ConfigurationID, VariableID)     


select * from 
(select d.filename, p.path, dt.TypeName as [Category], s.Name as [State],
[Document Description], [Doc Number], [Sales Order No], [Customer]
from Documents d
inner join DocumentsInProjects dp on d.DocumentID = dp.DocumentID
inner join projects p on p.ProjectID = dp.ProjectID  
inner join DocType dt on d.DocTypeID = dt.DocTypeID   
inner join status s on s.statusid = d.currentstatusid  
inner join
(SELECT vv.DocumentID, vv.ConfigurationID, VariableName, ValueText
    FROM VariableValue vv
    inner join Variable v on vv.VariableID = v.VariableID
    inner join maxvars mv on vv.DocumentID = mv.DocumentID and mv.ConfigurationID = vv.ConfigurationID
  and mv.VariableID = vv.VariableID and mv.RevisionNo = vv.RevisionNo
    ) AS SourceTable 


PIVOT
(
MAX(ValueText)
FOR VariableName IN ([Document Description], [Doc Number], [Sales Order No], [Customer])
) AS PivotTable on PivotTable.DocumentID = d.DocumentID  
where d.Deleted = 0
) a
where Path = '\EPDMVault\Projects\Current\'
and (Category = 'Procedure' or Category = 'Certificate')
                          • Re: Is there a sql query, that list all files, which belong to selected EPDM categorise?
                            Sebastian Soyke
                            Sebastian Soyke Jan 23, 2017 4:15 AM (in response to Charley Saint)

                            Thank you, Charley Saint .  I tried my best to sort the code but unfortunately, as I am not a programmer I have to learn the hard way and to troubleshoot and ask for help .

                            For some reason, the code is still not right. The syntax is ok, I assume as I have no errors, but I don't have any results. I tried many different variations, for example, I started with the "Path", and now I just left it as "p.path".

                            Would the issue be related to the formatting of the file path? Would I need a wildcard or something like that?

                             

                            with maxvars as

                            (select documentid, MAX(RevisionNo) RevisionNo, configurationid, variableid

                            from VariableValue group by DocumentID, ConfigurationID, VariableID)

                             

                            select [Sales Order No], [Customer], [Doc Number], [Document Description],

                            [Priority], [Document Surce], [Revision] as [Rev.], s.Name as [State],  [Submittal Date],

                            [Returned Date], [Approval Dedline Date] as [Dedline for approval], [Shopfloor Document Pack] as [Doc.pack], [Lead Engineer], [Sales Person], [Document Type VDRL] as [Doc. Type],

                            dt.TypeName as [Document Category], [Comment] as [Comments], d.filename, [Submittal Due Date], [Return Due Date], p.path

                             

                            from Documents d

                            inner join DocumentsInProjects dp on d.DocumentID = dp.DocumentID 

                            inner join projects p on p.ProjectID = dp.ProjectID   

                            inner join DocType dt on d.DocTypeID = dt.DocTypeID    

                            inner join status s on s.statusid = d.currentstatusid   

                            inner join

                            (SELECT vv.DocumentID, vv.ConfigurationID, VariableName, ValueText

                                FROM VariableValue vv

                                inner join Variable v on vv.VariableID = v.VariableID

                                inner join maxvars mv on vv.DocumentID = mv.DocumentID and mv.ConfigurationID = vv.ConfigurationID

                              and mv.VariableID = vv.VariableID and mv.RevisionNo = vv.RevisionNo

                                ) AS SourceTable

                             

                            PIVOT

                            (

                            MAX(ValueText)

                            FOR VariableName IN ([Document Description], [Doc Number], [Sales Order No], [Customer], [Priority], [Document Surce], [Revision], [Submittal Due Date],

                            [Submittal Date], [Return Due Date],  [Returned Date], [Lead Engineer], [Sales Person], [Approval Dedline Date],

                            [Document Type VDRL],[Shopfloor Document Pack], [Comment] )

                            ) AS PivotTable on PivotTable.DocumentID = d.DocumentID

                            where d.deleted = 0

                             

                            and p.Path = '\Projects\Current' 

                            and (dt.TypeName = 'VDRL Document' or dt.TypeName = 'Project Procedure' or dt.TypeName = 'Project Certificate')