I've been working on a SQL Query that will function as a robust keyword search. I was hoping to get some help in looking at this Query to simplify it. I think it's too complicated, but is the closest to achieving my desired results. Currently seems to return same results as EPDM Search, I think lol.
Assumptions:
1) I don't know what field to search on, a search card is linked to individual variables. I want a google style box. I know there is something similar in EPDM.
2) I want my search criteria to span all variables, all configurations, return the Variables where the search criteria was found in.
3) return a filename/documentID only if that search criteria is used in the latest version of that file.
4) Tables Variablevalue,Documents,Variable.
This is more of a time filler exercise, final output will probably be a Google style company web-app. I drove myself crazy with Joins, but finally went with storing a DataSet 1st then searching that DS as it held only my master search results.
USE <VaultName>
;WITH DS AS (SELECT DocumentID,VariableID,ValueText,RevisionNo FROM VariableValue WHERE ValueText LIKE '%<SearchME>%')
SELECT DS.*,V.RevisionNo,D.FileName,R.VariableName FROM DS,VariableValue V,Documents D,Variable R
WHERE V.DocumentID=DS.DocumentID AND V.VariableID=DS.VariableID AND
V.DocumentID=d.DocumentID AND V.VariableID=R.VariableID AND
DS.RevisionNo=V.RevisionNo AND
DS.RevisionNo=(SELECT MAX(Vr.RevisionNO) FROM VariableValue Vr WHERE Vr.DocumentID=DS.DocumentID AND Vr.VariableID=DS.VariableID)
ORDER BY V.VariableID,V.DocumentID
My DB is not huge, so it's hard for me to see how this query will perform. If anyone has a query that does this and wants to share, please do. I tend to over complicate things.
Above Query only works if you enter one search criteria. That's not very helpful. This updated Query builds a concatinated field of all variables that match a criteria (with the same documentID). I suspect the performance of this query will not be that great in a year or two.
What is the best out of the box method of providing only a single text box for users to enter key words and have EPDM search ALL VARIABLES for them with an AND condition. A user may want to search on a workstate, description, user etc...
For completeness... this Query works well. It runs with an Add-In that displays a results dialog box. A user double clicks a result and launches explorer with the file selected (Browse To Functionality).
;WITH DS AS (SELECT DocumentID,
STUFF((SELECT DISTINCT ', ' + CAST(ValueText as varchar(6000))
FROM VariableValue T2
WHERE T2.DocumentID = T1.DocumentID AND (ValueText LIKE '%WELDMENT%' OR ValueText LIKE '%RM%')
FOR XML PATH('')),1,1,'') AS AllValue
from VariableValue T1 group by DocumentID)
SELECT D.FileName,DS.* FROM Documents D, DS WHERE
DS.DocumentID=D.DocumentID AND
DS.AllValue IS NOT NULL AND DS.AllValue LIKE '%WELDMENT%' AND DS.AllValue LIKE '%RM%'