COALESCE_TABLE Function
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. SharePoint Portal Server queries through the COALESCE_TABLE function, where you specify both exact-match and freetext-match search conditions. The COALESCE_TABLE function is part of the FROM clause, and its syntax is:
COALESCE_TABLE (
<select_statement>
[ ; <select_statement>
)
No more than one COALESCE_TABLE function is allowed in each query. If the COALESCE_TABLE function is used in the FROM clause, no other scope specifiers are allowed in that FROM clause (excluding the scope specifiers contained in the select_statements).
With some restrictions, each select_statement is a complete SELECT statement that uses either the CONTAINS or FREETEXT predicates. In a COALESCE_TABLE function, 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 is:
<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 is:
<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
The asterisk (*) can be used 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. Asterisks cannot be used 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.
Examples
The following FROM clause example searches for the phrase "software update" in the SharePoint Portal Server BestBetsKeywords column. The search is performed on the contents of which are filtered and saved in the WebSites catalog. The returned documents are sorted by file size.
FROM COALESCE_TABLE (
SELECT *
FROM WebSites..SCOPE (' DEEP TRAVERSAL OF "http://www.microsoft.com" ' )WHERE CONTAINS ( "urn:schemas-microsoft-com:publishing:BestBetKeywords" ,'software update' )ORDER BY "DAV:getcontentlength"
;SELECT *FROM WebSites..SCOPE (' DEEP TRAVERSAL OF "http://www.microsoft.com" ' )WHERE FREETEXT ( "urn:schemas-microsoft-com:publishing:BestBetKeywords" ,'software update' )ORDER BY "DAV:getcontentlength"
)