WITH -- AS Group Alias Predicate
Column group aliases provide a way to use shorter names in place of the name of a column or a group of columns. The optional group alias predicate is part of the WHERE clause, and its syntax follows:
…WHERE[ WITH(<columns>) AS #<alias_name>]
[,WITH(<columns>) AS #<alias_name>]
You can specify more than one group alias, separating the WITH…AS predicates by commas.
When a group alias is referred to in a WHERE clause predicate, the condition is applied to each column in the group. The logical values resulting from matching each column are combined by using the OR logical operator.
The alias must be defined before it can be used, and it can only be used within the WHERE clause. The alias_name must be a regular identifier preceded with a required pound sign (#).
Note Only the FREETEXT predicate supports column grouping and aliases by using the WITH…AS predicate. The CONTAINS predicate does not support column grouping and aliasing.
The columns specifier can contain one or more column specifiers, separated by commas. The list of columns must be enclosed in parentheses and weighting can be assigned to each. Each column has the following syntax:
<column_identifier> [<weight_assignment>]
For information on specifying column weights, see FREETEXT Predicate and CONTAINS Predicate.
The column identifier can be regular or delimited.
Examples
The following WHERE clause examples demonstrate when you could use the group alias predicate.
…WHERE
FREETEXT(
"urn:schemas-microsoft-com:publishing:BestBetKeywords",
'"computer software"')
OR
FREETEXT(
"urn:schemas-microsoft-com:office:office#Title",
'"computer software"')
OR
FREETEXT(
"urn:schemas-microsoft-com:office:office#Comments",
'"computer software"')
OR CONTAINS(
"urn:schemas-microsoft-com:office:office#Keywords"
'"computer""software"')
The preceding example can be simplified by using a group alias, as shown in the following example.
…WHERE
WITH(
"urn:schemas-microsoft-com:publishing:BestBetKeywords",
"urn:schemas-microsoft-com:office:office#Title",
"urn:schemas-microsoft-com:office:office#Comments",
"urn:schemas-microsoft-com:office:office#Keywords")
AS #Doc-Descriptions
FREETEXT(#Doc-Descriptions,'"computer software"')
The following is an example of positive weighting where the Title property is given more weight in determining the relative rank.
…WHERE
WITH(
"urn:schemas-microsoft-com:office:office#Title":0.8,*:0.5,
"urn:schemas-microsoft-com:office:office#Keywords")
AS #Doc-Descriptions
FREETEXT(#Doc-Descriptions,'"computer software"')
The following is an example of negative weighting where the Title property with weight of 0 is not considered.
…WHERE
WITH(
"urn:schemas-microsoft-com:office:office#Title":0,*:1.0,
"urn:schemas-microsoft-com:office:office#Keywords")
AS #Doc-Descriptions
FREETEXT(#Doc-Descriptions,'"computer software"')