SAP SQL Anywhere

Question

using sa 11.0.1.2331

This works fine:

SELECT cms_content.*
   FROM dba.cms_content
   WHERE CONTAINS (  title, intro, body, footer , 'hello' )

According to the docs, this should retrieve scoring info:

SELECT cms_content.*, ct.*
   FROM dba.cms_content
   WHERE CONTAINS (  title, intro, body, footer , 'hello' ) ct
order by ct.score desc

But iSQL reports a "syntax error near ct on line 3"

What am I missing here?

PS: I tried variations on

SELECT cms_content.*, "contains".score
   FROM dba.cms_content
   WHERE CONTAINS (  body , 'hello' )

but all I get is syntax errors there as well

Accepted Answer

Scoring is supported only if the CONTAINS clause is part of the table expression (specified in the FROM clause). Only if specified as a table expression is the implicit "score" column added to the projection of the underlying table. For example,

SELECT *
FROM MarketingInformation CONTAINS ( Description, 'cotton' ) as CT
WHERE CT.score > <some value>;

By adding a correlation name, you can use multiple CONTAINS table expressions within the same query, and refer to each search score independently by qualifying the "score" column by the given correlation name.

SAP SQL Anywhere

retrieving scores in full text searches

Follow this question

Related questions