Full text indexing and contains

Question

I am trying to use CONTAINS on a full-text indexed view. This works great if I create a variable and pass the value in that way like this

    DECLARE @ThresholdString VARCHAR(50) = 'Ganja';

    SELECT *
    FROM vw_CMP_Invoice_Data_Indexed
    WHERE CONTAINS((ItemName,Description),@ThresholdString);

However, I am trying to cross apply a temp table to this view and use a field that comes from this temp table as the value I am searching for:

    SELECT  f.AlertId,ci.BankISN,ci.EntityISN,ci.AccountISN,ci.InvoiceID,f.ThresholdType,f.TimeFrame,f.ThresholdString,ci.ItemName,ci.Description
    FROM    dbo.vw_CMP_Invoice_Data_Indexed ci
    CROSS APPLY (SELECT * 
                FROM #final f
                WHERE ci.BankISN = f.BankId
                AND ci.EntityISN = f.EntityId
                AND CONVERT(DATE,ci.InvoiceDate) = CONVERT(DATE,@RunDate)
                AND CONTAINS((ci.ItemName,ci.Description),ThresholdString)
                ) f --This is wrong, not sure if I can actually use a value that is cross applied here...
    GROUP BY f.AlertId,ci.BankISN,ci.EntityISN,ci.AccountISN,ci.InvoiceID,f.ThresholdType,f.TimeFrame,f.ThresholdString

Is it not possible to use a field as the search value using contains on a full-text indexed view, or do I just have the syntax wrong?

I've also tried:

    SELECT  f.AlertId,ci.BankISN,ci.EntityISN,ci.AccountISN,ci.InvoiceID,f.ThresholdType,f.TimeFrame,f.ThresholdString,ci.ItemName,ci.Description
    FROM    dbo.vw_CMP_Invoice_Data_Indexed ci
    CROSS APPLY (SELECT * 
                FROM #final f
                WHERE ci.BankISN = f.BankId
                AND ci.EntityISN = f.EntityId
                AND CONVERT(DATE,ci.InvoiceDate) = CONVERT(DATE,@RunDate)
                AND CONTAINS((ci.ItemName,ci.Description),ThresholdString)
                ) f --This is wrong, not sure if I can actually use a value that is cross applied here...
    GROUP BY f.AlertId,ci.BankISN,ci.EntityISN,ci.AccountISN,ci.InvoiceID,f.ThresholdType,f.TimeFrame,f.ThresholdString

AND

    SELECT  f.AlertId,ci.BankISN,ci.EntityISN,ci.AccountISN,ci.InvoiceID,f.ThresholdType,f.TimeFrame,f.ThresholdString,ci.ItemName,ci.Description
    FROM    dbo.vw_CMP_Invoice_Data_Indexed ci
    CROSS APPLY (SELECT * 
                FROM #final f
                WHERE ci.BankISN = f.BankId
                AND ci.EntityISN = f.EntityId
                AND CONVERT(DATE,ci.InvoiceDate) = CONVERT(DATE,@RunDate)
                ) f --This is wrong, not sure if I can actually use a value that is cross applied here...
    WHERE CONTAINS((ci.ItemName,ci.Description),f.ThresholdString)
    GROUP BY f.AlertId,ci.BankISN,ci.EntityISN,ci.AccountISN,ci.InvoiceID,f.ThresholdType,f.TimeFrame,f.ThresholdString

But they both show this error message

> Msg 102, Level 15, State 1, Procedure InvoiceProductKeyword, Line 101 [Batch Start Line 0]
    Incorrect syntax near 'ThresholdString'.

Sorry, that looks horrible and I cannot see how I can edit it for better formatting — jwb, Jul 22 '19 at 18:38
See here: https://stackoverflow.com/questions/17448269/using-join-statement-with-contains-function — Joel Coehoorn, Jul 22 '19 at 18:43
@JoelCoehoorn Thanks, I was afraid that was the case. I went ahead and took the lame way out for now and just used LIKE... I will try to revisit at another time to try one of the 'clever' ways to see if I can take advantage of the Full-Text Indexing in this instance. Thanks again! — jwb, Jul 23 '19 at 19:34

score 0 · Answer 1 · answered Jul 23 '19 at 15:14

0

Looks like you are missing a right parenthesis... change

WHERE CONTAINS((ci.ItemName,ci.Description),f.ThresholdString)

To

WHERE CONTAINS((ci.ItemName,ci.Description),f.ThresholdString))

answered Jul 23 '19 at 15:14

Alan Burstein

7,770
1
15
18

No, that doesn't do it. Thanks, though! – jwb Jul 23 '19 at 19:35

Full text indexing and contains

1 Answers1