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

      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.

        • Re: Is there a sql query, that list all files, which belong to selected EPDM categorise?
          Charley Saint

          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

              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

                  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

                      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

                          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

                    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)     
                    4. select d.filename, dt.TypeName as [Category], s.Name as [State],
                    5. [Document Description], [Doc Number], [Sales Order No], [Customer]
                    6. from Documents d  
                    7. inner join DocType dt on d.DocTypeID = dt.DocTypeID   
                    8. inner join status s on s.statusid = d.currentstatusid  
                    9. inner join
                    10. (SELECT vv.DocumentID, vv.ConfigurationID, VariableName, ValueText
                    11.     FROM VariableValue vv
                    12.     inner join Variable v on vv.VariableID = v.VariableID
                    13.     inner join maxvars mv on vv.DocumentID = mv.DocumentID and mv.ConfigurationID = vv.ConfigurationID
                    14.   and mv.VariableID = vv.VariableID and mv.RevisionNo = vv.RevisionNo
                    15.     ) AS SourceTable
                    16.     
                    17. PIVOT
                    18. (
                    19. MAX(ValueText)
                    20. FOR VariableName IN ([Document Description], [Doc Number], [Sales Order No], [Customer])
                    21. ) AS PivotTable on PivotTable.DocumentID = d.DocumentID
                    22. where d.deleted = 0
                    23. and d.filename= (SELECT d.filename FROM Documents d WHERE Path '\EPDMVault\Projects\Current\')
                    24. 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

                        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

                            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')