The WHERE function can be used to provide
filtering on a path used in a grouping expression. Most importantly, it can be used for multi-clause expressions that are
bound to the same objects. To illustrate why the
WHERE clause is needed and how it is used, here’s an example.
This query doesn’t work because it selects messages for which at least one recipient’s
ReceiptDate is >
"2014‑01‑01", and
at least one recipient has an external domain. Every such message is then counted in
all of its
Recipients.MessageAddress.Domain.Name values, even for those that don’t really qualify.
Using the WHERE filter for query expressions, we could bind the two query clauses to the same
Recipients instances. But this query still doesn’t work:
For this scenario, we can use the WHERE function in the grouping parameter instead of the query parameter. In a grouping parameter, the
WHERE function filters out group values we don’t want. And, when the object selection criteria lies solely in the choice of groups, we don’t need a separate query parameter. The solution to the previous problem can be expressed as follows:
The grouping field is still Recipients.MessageAddress.Domain.Name, but the
WHERE function inserted after
Recipients filters values used for grouping. The first field in each
WHERE clause (
ReceiptDate and
MessageAddress) must be members of the same table as
Recipients, thereby filtering the recipients in some manner. In this case, only recipients whose
ReceiptDate is >
"2014‑01‑01" and whose
MessageAddress.Domain.IsInternal is false. Groups are created by domains of recipients that match those constraints, and only objects within those group values are counted.
But wait! It gets better! The WHERE function can be applied to multiple components of the same grouping path as long as each subquery is qualified to the path component to which it is attached. Exploiting this, we can factor out the redundant specification of
MessageAddress.Domain with this shorter but equivalent expression: