February 4, 2009 at 9:56 am
I have a query that I use for searching that contains a FREETEXTTABLE statement.
I now need another query to search another table, but this table is not text indexed.
I tried to copy the existing query to a new query, and then modifying it because it can't use FREETEXTTABLE.
Here is what I ended up with. I need to figure out how to treat the nested query as a table(ftt).
SELECT ftt.RANK,
'time objectives' AS RESULTCATEGORY,
pl.personDisplayName AS RESULTTITLE,
CONVERT(varchar(255), to.[text]) AS RESULTTEXT,
to.personID AS RESULTID,
FROM (SELECT objectiveText FROM timeObjectives WHERE objectiveText LIKE @searchstring) ftt
INNER JOIN timeObjectives to
ON ftt. = to.objectiveID)
INNER JOIN personList pl
ON to.personID = pl.personID
It keeps erroring out at ') ftt'
I assume because I can't use a nested query as a table.
If anyone could offer any suggestions, I would greatly appreciate it.
Thanks,
M
February 4, 2009 at 10:08 am
Magy (2/4/2009)
I have a query that I use for searching that contains a FREETEXTTABLE statement.I now need another query to search another table, but this table is not text indexed.
I tried to copy the existing query to a new query, and then modifying it because it can't use FREETEXTTABLE.
Here is what I ended up with. I need to figure out how to treat the nested query as a table(ftt).
SELECT ftt.RANK,
'time objectives' AS RESULTCATEGORY,
pl.personDisplayName AS RESULTTITLE,
CONVERT(varchar(255), to.[text]) AS RESULTTEXT,
to.personID AS RESULTID,
FROM (SELECT objectiveText FROM timeObjectives WHERE objectiveText LIKE @searchstring) ftt
INNER JOIN timeObjectives to
ON ftt. = to.objectiveID)
INNER JOIN personList pl
ON to.personID = pl.personID
It keeps erroring out at ') ftt'
I assume because I can't use a nested query as a table.
If anyone could offer any suggestions, I would greatly appreciate it.
Thanks,
M
One, you can use a derived table in the FROM clause. Two, you may actually have two problems with your query above. The first is that the derived table is only returning one column (objectiveText). the second is that i think your query is actually failing on the @searchstring in the derived table.
If you could provide the DDL for the tables, sample data for the tables (as INSERT statements that can be cut, paste, and executed in SSMS/EM), and the expected results based on the provided sample data we may be able to help.
For more information on how to get better answers to your questions, please read the first article linked below in my signature block regarding asing for assistance.
February 4, 2009 at 12:43 pm
Thanks...I figured that the best way to do this was just add the needed table to the full text catalog. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply