AnsweredAssumed Answered

SQL query for multiple epdm variable values in a file

Question asked by Tara Campese on Jan 11, 2017
Latest reply on Jan 13, 2017 by Tara Campese

I created a View that makes a table showing a document's file name, revision variable, document ID, project ID, cleaning variable, revision number, and part number variable.  However, there seems to be a row for each value of these variables.

Here's my View:

SELECT     dbo.Documents.Filename, VariableValue_1.ValueText AS [DRW Rev], dbo.Documents.DocumentID, dbo.DocumentsInProjects.ProjectID,

                      VariableValue_2.ValueText AS [Cleaning Required], VariableValue_1.RevisionNo, dbo.VariableValue.ValueText AS [Part Number]

FROM         dbo.Documents INNER JOIN

                      dbo.VariableValue ON dbo.Documents.DocumentID = dbo.VariableValue.DocumentID INNER JOIN

                      dbo.Variable ON dbo.VariableValue.VariableID = dbo.Variable.VariableID INNER JOIN

                      dbo.VariableValue AS VariableValue_1 ON dbo.Documents.DocumentID = VariableValue_1.DocumentID INNER JOIN

                      dbo.Variable AS Variable_1 ON VariableValue_1.VariableID = Variable_1.VariableID INNER JOIN

                      dbo.VariableValue AS VariableValue_2 ON dbo.Documents.DocumentID = VariableValue_2.DocumentID INNER JOIN

                      dbo.Variable AS Variable_2 ON VariableValue_2.VariableID = Variable_2.VariableID INNER JOIN

                      dbo.DocumentsInProjects ON dbo.Documents.DocumentID = dbo.DocumentsInProjects.DocumentID

WHERE     (dbo.Documents.Filename LIKE '%.slddrw') AND (dbo.Variable.VariableName LIKE 'Part Number') AND (dbo.VariableValue.ValueText LIKE 'RBT-%') AND

                      (Variable_1.VariableName LIKE 'Revision') AND (Variable_2.VariableName LIKE 'Cleaning Required') AND (dbo.Documents.Deleted = 0) AND

                      (dbo.Documents.Shared <> 0) AND (dbo.Documents.DocTypeID = 1) AND (NOT (VariableValue_1.ValueText LIKE '')) AND (NOT (VariableValue_2.ValueText LIKE '')) AND

                      (NOT (dbo.VariableValue.ValueText LIKE ''))

Here is the query that uses it:

select * from dbo.drawingbypartno

where [Part Number] like '%-00B876%'

order by RevisionNo desc

Here are my results:

 

What I want to have is something that shows one line for each RevisionNo and it should be tied to the corresponding values for the file that is at that revision. In the image above, the correct line for RevionNo 12 is line 5. Looking into SQL commands, I think I need to use the UNION command, but I can't quite figure out how to format it.

 

Any help would be appreciated.

 

Thank you,

Tara

Outcomes