3 Replies Latest reply on Jan 11, 2017 8:58 AM by Steve Ostrovsky

    Sql Query search Restriced to specified folder

    Sebastian Soyke

      Dear All,


      2 years ago Charley Saint, did build the below 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. Please help if you have an idea where is my mistake.



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