Invalid results in FULLTEXTTABLE Search

Help.
Have the following:
SELECT
(ISNULL(CAST(RANKTBL1.RANK AS decimal),0)) +
(ISNULL(CAST(RANKTBL2.RANK AS decimal),0)/10) AS RANK,
RANKTBL1.RANK AS RANK1,
RANKTBL2.RANK AS RANK2,
items.itempkid,
items.keywords,
menu.menupkid,
menu.title,
items.content
FROM
(menu INNER JOIN items ON menu.itempkid = items.itempkid)
INNER JOIN
(FREETEXTTABLE(items,keywords,'paul drew') AS RANKTBL1 FULL OUTER
JOIN FREETEXTTABLE(items,content,'paul drew') AS RANKTBL2 ON
RANKTBL1.[KEY] = RANKTBL2.[KEY])
ON (items.itempkid = RANKTBL1.[KEY] OR items.itempkid =
RANKTBL2.[KEY])
WHERE
(ISNULL(CAST(RANKTBL1.RANK AS decimal),0)/10) +
(ISNULL(CAST(RANKTBL2.RANK AS decimal),0)/10) > 3
ORDER BY
RANK DESC
But the highest ranked record this returns from my database has
neither the word 'paul' or 'drew' occuring in any of the fields. The
closest I can find is that the top record has 'dre' near the beginning
of the 'content' field.
If I just search for 'drew' this excludes 2 records that contain the
word 'drew' and still returns the aforementioned record.
I've tried doing a full population, and rebuilding the catalog+full
population with exactly the same results returned.
Any ideas?
Andy,
Could you provide the full output of -- SELECT @.@.version -- as this would be
helpful in troubleshooting this SQL FTS issue.
Also, could you post the count(*) value for your FT-enabled table items?
Additionally, while I understand why you're doing the "math" on the returned
RANK values, but it will not get you a higher RANK value for specific words
or phrases that you expect. See SQL Server 2000 BOL title "Full-Text Search
Recommendations" for a more information. As an alternative, you may want to
try the below approach - assuming that your table as a statistically
significantly number of rows - and using the WEIGHT parameter:
SELECT e.LastName, e.FirstName, e.Title, e.Notes, B.[KEY], B.[RANK] as
B_RANK, A.[RANK] as A_RANK
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
containstable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID and
B.[KEY] = e.EmployeeID
--> examples of using RANK and multipling RANK
use pubs
go
SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK, key_tbl1.Rank
FROM authors as FT_TBL,
CONTAINSTABLE (authors,au_lname, 'Ringer' ) AS KEY_TBL,
CONTAINSTABLE (authors,au_fname, 'Michael' ) AS KEY_TBL1
WHERE
FT_TBL.au_id = KEY_TBL.[KEY] or
FT_TBL.au_id = KEY_TBL1.[KEY] and
key_tbl1.Rank < 2* KEY_TBL.RANK
ORDER BY KEY_TBL.Rank, KEY_TBL1.RANK
Regards,
John
"Andy Clark" <andy@.workonmypc.co.uk> wrote in message
news:718c4cf7.0405050614.f103787@.posting.google.co m...
> Help.
> Have the following:
> ----
--
> SELECT
> (ISNULL(CAST(RANKTBL1.RANK AS decimal),0)) +
> (ISNULL(CAST(RANKTBL2.RANK AS decimal),0)/10) AS RANK,
> RANKTBL1.RANK AS RANK1,
> RANKTBL2.RANK AS RANK2,
> items.itempkid,
> items.keywords,
> menu.menupkid,
> menu.title,
> items.content
> FROM
> (menu INNER JOIN items ON menu.itempkid = items.itempkid)
> INNER JOIN
> (FREETEXTTABLE(items,keywords,'paul drew') AS RANKTBL1 FULL OUTER
> JOIN FREETEXTTABLE(items,content,'paul drew') AS RANKTBL2 ON
> RANKTBL1.[KEY] = RANKTBL2.[KEY])
> ON (items.itempkid = RANKTBL1.[KEY] OR items.itempkid =
> RANKTBL2.[KEY])
> WHERE
> (ISNULL(CAST(RANKTBL1.RANK AS decimal),0)/10) +
> (ISNULL(CAST(RANKTBL2.RANK AS decimal),0)/10) > 3
> ORDER BY
> RANK DESC
> ----
--
> But the highest ranked record this returns from my database has
> neither the word 'paul' or 'drew' occuring in any of the fields. The
> closest I can find is that the top record has 'dre' near the beginning
> of the 'content' field.
> If I just search for 'drew' this excludes 2 records that contain the
> word 'drew' and still returns the aforementioned record.
> I've tried doing a full population, and rebuilding the catalog+full
> population with exactly the same results returned.
> Any ideas?
|||SELECT @.@.version:
Microsoft SQL Server 7.00 - 7.00.623 (Intel X86) Nov 27 1998
22:20:07 Copyright (c) 1988-1998 Microsoft Corporation Standard
Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
COUNT(*) of items:
353
The "math" on the RANK values is to weight the importance of different
fields, i.e. keywords are more important than content and therefore
have a greater influence in the overall RANK.
"John Kane" <jt-kane@.comcast.net> wrote in message news:<#cGlT9wMEHA.3944@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
> Andy,
> Could you provide the full output of -- SELECT @.@.version -- as this would be
> helpful in troubleshooting this SQL FTS issue.
> Also, could you post the count(*) value for your FT-enabled table items?
> Additionally, while I understand why you're doing the "math" on the returned
> RANK values, but it will not get you a higher RANK value for specific words
> or phrases that you expect. See SQL Server 2000 BOL title "Full-Text Search
> Recommendations" for a more information. As an alternative, you may want to
> try the below approach - assuming that your table as a statistically
> significantly number of rows - and using the WEIGHT parameter:
> SELECT e.LastName, e.FirstName, e.Title, e.Notes, B.[KEY], B.[RANK] as
> B_RANK, A.[RANK] as A_RANK
> from Employees AS e,
> containstable(Employees, Notes, 'ISABOUT (BA weight (.2) )') as A,
> containstable(Employees, Title, 'Sales') as B
> where
> A.[KEY] = e.EmployeeID and
> B.[KEY] = e.EmployeeID
> --> examples of using RANK and multipling RANK
> use pubs
> go
> SELECT FT_TBL.au_lname, FT_TBL.au_fname, KEY_TBL.RANK, key_tbl1.Rank
> FROM authors as FT_TBL,
> CONTAINSTABLE (authors,au_lname, 'Ringer' ) AS KEY_TBL,
> CONTAINSTABLE (authors,au_fname, 'Michael' ) AS KEY_TBL1
> WHERE
> FT_TBL.au_id = KEY_TBL.[KEY] or
> FT_TBL.au_id = KEY_TBL1.[KEY] and
> key_tbl1.Rank < 2* KEY_TBL.RANK
> ORDER BY KEY_TBL.Rank, KEY_TBL1.RANK
> Regards,
> John
>
> "Andy Clark" <andy@.workonmypc.co.uk> wrote in message
> news:718c4cf7.0405050614.f103787@.posting.google.co m...
> --
> --
|||Thanks, Andy,
Yes, I suspected that your query was to have on one column "rank" higher
than the other. However, you will never get the expected results with only
353 rows. You should re-test your query on a table with at least 50,000 to
100,000 rows in order to get to a statistically significantly number of
rows.
Regards,
John
"Andy Clark" <andy@.workonmypc.co.uk> wrote in message
news:718c4cf7.0405060223.f3a5d3b@.posting.google.co m...
> SELECT @.@.version:
> Microsoft SQL Server 7.00 - 7.00.623 (Intel X86) Nov 27 1998
> 22:20:07 Copyright (c) 1988-1998 Microsoft Corporation Standard
> Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
> COUNT(*) of items:
> 353
>
>
> The "math" on the RANK values is to weight the importance of different
> fields, i.e. keywords are more important than content and therefore
> have a greater influence in the overall RANK.
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<#cGlT9wMEHA.3944@.tk2msftngp13.phx.gbl>...[vbcol=seagreen]
would be[vbcol=seagreen]
returned[vbcol=seagreen]
words[vbcol=seagreen]
Search[vbcol=seagreen]
to[vbcol=seagreen]
> ----
> ----
|||But surely this still doesn't explain the search coming up with a high
rank result that doesn't contain any of the search terms. Would
applying SQL Server 7 SP4 help?
"John Kane" <jt-kane@.comcast.net> wrote in message news:<uQFHNq3MEHA.3052@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> Thanks, Andy,
> Yes, I suspected that your query was to have on one column "rank" higher
> than the other. However, you will never get the expected results with only
> 353 rows. You should re-test your query on a table with at least 50,000 to
> 100,000 rows in order to get to a statistically significantly number of
> rows.
> Regards,
> John
>
> "Andy Clark" <andy@.workonmypc.co.uk> wrote in message
> news:718c4cf7.0405060223.f3a5d3b@.posting.google.co m...
> news:<#cGlT9wMEHA.3944@.tk2msftngp13.phx.gbl>...
> would be
> returned
> words
> Search
> to
> ----
> ----
|||Andy,
While you may want to apply SQL Server 7.0 service pack SP4 for other
reasons, this is not one of them.
You should re-test your query using a much larger table. Additionally, if
you plan to use SQL 7.0 FTS in a production environment, I'd highly
recommend that you consider upgrading to SQL Server 2000 as there are a
number of fixes, feature enhancements and performance improvements in SQL
2000 that are not in SQL 7.0
Regards,
John
"Andy Clark" <andy@.workonmypc.co.uk> wrote in message
news:718c4cf7.0405070446.678531b9@.posting.google.c om...
> But surely this still doesn't explain the search coming up with a high
> rank result that doesn't contain any of the search terms. Would
> applying SQL Server 7 SP4 help?
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<uQFHNq3MEHA.3052@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
only[vbcol=seagreen]
to[vbcol=seagreen]
items?[vbcol=seagreen]
specific[vbcol=seagreen]
want[vbcol=seagreen]
as[vbcol=seagreen]
A,
>
----
>
----[vbcol=seagreen]
The[vbcol=seagreen]
beginning[vbcol=seagreen]
the[vbcol=seagreen]
catalog+full[vbcol=seagreen]

Invalid column name

Below is the T-SQL

SELECT ci.name,
ci.address,
CASE ISNULL(cci.geography,'')
WHEN 'P' THEN 'port'
WHEN 'A' THEN 'appt'
WHEN 'X' THEN 'xatt'
ELSE ''
END AS Link,
ci.InsuranceType
FROM dbo.Insurance ci
INNER JOIN dbo.Contract cci
ON ci.InsuranceId = cci.InsuranceId
AND cci.ContractId = 1266
ORDER BY
ci.Link,
ci.InsuranceType

===========================

My work environment is all in SQL SERVER 2005.

I have a problem with above T-SQL. There is an error while I run above code in Develoment Server.The error disappears when I put 'ci.Link' as 'Link' in OrderBy clause. My work environment has TEST, PRE-PRODUCTION and PRODUCTION. In all other environments the code runs fine with ci.Link but only in DEVELOPMENT the error persists. It has an error as 'Link is invalid column name'.

Hi madhav,

You could use is clause:

SELECT ...
ORDER BY 2 ASC, 3 ASC;

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

Thanks Javier for your response. My all work environments are same( from DEV to PROD). Without adding a single line or changing anything, above code runs fine in all but fails only in DEVELOPMENT. But runs if you put as Link only as mentioned above.

|||

Cool.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

The error happens because of invalid use of expressions in the ORDER BY clause. Older versions had several bugs where expressions in ORDER BY clause was allowed or wrongly evaluated to those in the FROM clause. But SQL Server 2005 corrects the behavior and is more closer towards standard SQL syntax. And depending on compatibility level (<90) the query will work fine. So on your servers other than DEVELOPMENT the database compatibility level is not 90 hence the query runs fine. See below link for more details (search for "ORDER BY clause"):

http://msdn2.microsoft.com/en-us/library/ms143359.aspx

sp_dbcmptlevel topic:

http://msdn2.microsoft.com/en-us/library/ms178653.aspx

|||

Your answer solved the problem. Much appreciated.