AnsweredAssumed Answered

SQL help to get Filename and Description from EPDM

Question asked by Guy Edkins on Sep 21, 2017
Latest reply on Sep 21, 2017 by Steven Dod



I have a standalone in-house app I am creating that needs to query EPDM-SQL.  I need to hand in an EPDM users name and have it hand back to me all ECO files (we prefix the files name with ECO) and the description that goes along with each file. I know this data comes from the Documents and VariableValue tables. I have used this call  with some success - it returns all users with ECO's and the description. I am having a hell of a time handing in a users name and getting back just ECO's and the descriptions. Any one have some ideas?


SELECT       max(Case when VV1.VariableName = N'Author' then VariableValue.ValueText END) as Author,

                    max(Case when VV1.VariableName = N'Description' then VariableValue.ValueText END) AS Description

                         FROM Documents INNER JOIN

                         VariableValue ON Documents.DocumentID = VariableValue.DocumentID INNER JOIN

                         Variable AS VV1 ON VariableValue.VariableID = VV1.VariableID

                            WHERE (Documents.Filename like N'ECO-%') AND (Documents.Deleted = 0) and (VV1.VariableName in (N'Author', N'Description')

                              and (VariableValue.ValueText <> '-'))

                            Group BY Documents.Filename

                            ORDER BY Author asc