WITH -- AS Group Alias Predicate in SharePoint Search SQL Syntax

Last modified: July 20, 2010

Applies to: SharePoint Server 2010

Column group aliases that provide you with 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.

…WHERE[ WITH(<columns>) AS #<alias_name>] 

You can specify more than one group alias, separating the WITH…AS predicates by using 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.

You must define the alias 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 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 each column has the following syntax:

<column_identifier> [<weight_assignment>]

The column identifier can be regular or delimited.