The
WHERE clause is used to filter input records for an
expression.
EQL
provides two filtering options:
WHERE and
HAVING. The syntax of the
WHERE clause is as follows:
WHERE <BooleanExpression>
You can use the
WHERE clause with any Boolean expression, such as:
- Numeric and string value
comparison:
{= , <>, <, <=, >, >=}
- Set operations: such as
SUBSET and
IS_MEMBER_OF
- Null value evaluation:
<attribute> IS {NULL, NOT NULL} (for atomic
values) and
<attribute> IS {EMPTY, NOT EMPTY} (for sets)
- Grouping keys of the source
statement:
<attribute-list> IN <source-statement>.
The number and type of these keys must match the number and type of keys used
in the statement referenced by the
IN clause. For more information, see
IN.
Aliased attributes (from the
SELECT clause) cannot be used in the
WHERE clause, because
WHERE looks for an attribute in the source (such as the
corpus). Thus, this example:
RETURN results AS
SELECT
WineID AS id,
Score AS scores
WHERE id > 5
ORDER BY scores
is invalid and returns the error message:
In statement "results": In WHERE clause: This corpus does not have an attribute named "id" - Location:5:9-5:10
If an aggregation function is used with a
WHERE clause, then the Boolean expression must be
enclosed within parentheses. The aggregation functions are listed in the topic
Aggregation functions.
In this example, the amounts are only calculated for sales in the West
region. Then, within those results, only sales representatives who generated at
least $10,000 are returned:
RETURN Reps AS
SELECT SUM(Amount) AS SalesTotal
WHERE Region = 'West'
GROUP BY SalesRep
HAVING SalesTotal > 10000
In the next example, a single statement contains two expressions. The
first expression computes the total for all of the records and the second
expression computes the total for one specific sales representative:
RETURN QuarterTotals AS SELECT
SUM(Amount) As SalesTotal,
SUM(Amount) WHERE (SalesRep = 'Juan Smith') AS JuanTotal
GROUP BY Quarter
This would return both the total overall sales and the total sales for
Juan Smith for each quarter. Note that the Boolean expression in the
WHERE clause is in parentheses because it is used with
an aggregation function (SUM in this case).
The second example also shows how use a per-aggregate
WHERE clause:
SUM(Amount) WHERE (SalesRep = 'Juan Smith') AS JuanTotal
For more information on per-aggregate
WHERE filters, see
Per-aggregation filters.