AnsweredAssumed Answered

Sql Query search Restriced to specified folder

Question asked by Sebastian Soyke on Jan 11, 2017
Latest reply on Jan 11, 2017 by Steve Ostrovsky

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

Outcomes