0
SELECT * FROM tblA
--Result:
--[DocFileId]   Status
--1             0
--2             1
--3             0
--4             1
--5             1

SELECT * FROM FREETEXTTABLE(DB_B..DocOcrText, OcrText, 'Appointment', 5) as Key_Tbl
--Result:
--KEY       RANK
--1         500
--2         400
--3         300
--4         200
--5         200

SELECT * FROM tblA WHERE tblA.Status = 1
--Result:
--[DocFileId]   Status
--2             1
--4             1
--5             1

SELECT * FROM FREETEXTTABLE(DB_B..DocOcrText, OcrText, 'Appointment', 2) as Key_Tbl
--Result:
--KEY       RANK
--1         500
--2         400

SELECT * FROM tblA
INNER JOIN FREETEXTTABLE(DB_B..DocOcrText, OcrText, 'Appointment', 2) as Key_Tbl on tblA.[DocFileId] = Key_Tbl.[Key]
WHERE tblA.Status = 1
--Result:
--[DocFileId]   Status  KEY     RANK
--2             1       2       400

--EXPECTED RESULT 1:
--[DocFileId]   Status  KEY     RANK
--2             1       2       400
--4             1       4       200

--EXPECTED RESULT 2:
--[DocFileId]   Status  KEY     RANK
--2             1       2       400
--4             1       4       200
--5             1       5       200

This is a simplify version of my problem. On my production, the FREETEXTTABLE top rank are set to a large number, 2000.

EXPECTED RESULT 1
Is that anyway for me to pre-filtering the condition on tblA.Status = 1, then bring that result to do filtering on the FREETEXTTABLE(DB_B..DocOcrText, OcrText, 'Appointment', 2) to get my expected result 1?

EXPECTED RESULT 2
On the worse scenario, due to the key have the same ranking, example key 4 and 5 have the same ranking 200. The lowest will not be show due to the top rank setting on the FREETEXTTABLE, anyway for me to show both even its over the top rank already?

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.