COALESCE_TABLE Function
When users search for documents, if an exact match cannot be found, it is customary to return the closest matches that can be found. Microsoft SharePoint Portal Server Search (SharePointPSSearch) supports this in search queries through the COALESCE_TABLE function, where you specify both exact match and freetext match search conditions.
When you use the COALESCE_TABLE function, you get the results from the first SELECT statement that returns any results. It is part of the FROM clause, and its syntax follows:
COALESCE_TABLE(<select_statement>[;<select_statement])
You cannot use more than one COALESCE_TABLE function in each query. If you use the COALESCE_TABLE function in the FROM clause, you cannot use other scope specifiers in that FROM clause (excluding the scope specifiers contained in the SELECT statements).
With some restrictions, each SELECT statement within the function is a complete SELECT statement that uses either the CONTAINS or FREETEXT predicates. Each SELECT statement must have the same scope specification. To improve the search results, all the SELECT clauses should be similar enough that they return sensible results. For example, users requesting information about "computers" might be surprised to receive a list of documents describing "food".
The syntax for the SELECT statement using the CONTAINS predicate follows:
<SELECT_clause> <FROM_clause>
WHERE CONTAINS(<column>,'<simple_term>'[,<language>])
[<RANK_BY_clause>]
[<ORDER_BY_clause>]
The syntax for the SELECT statement using the FREETEXT predicate follows:
<SELECT_clause><FROM_clause>
WHERE FREETEXT(<column>,'<simple_term>'[,<language>])
[<RANK_BY_clause>]
[<ORDER_BY_clause>]
The column specifiers must use the asterisk (*) to indicate all rows. The outer SELECT statement specifies the columns returned to the application.
Important You can use the asterisk (*) to indicate columns only within the exact match SELECT or freetext match SELECT statements of a COALESCE_TABLE function. Asterisks in the COALESCE_TABLE function are allowed because the SELECT clause names the specific columns to return. You cannot use asterisks as column specifiers in the SELECT clause.
The COALESCE_TABLE function does not require that the simple term be the same in both the exact match SELECT and freetext match SELECT statements. However, the scope specifications must be identical in both SELECT statements.