WHERE clauses

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:
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
    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:
SELECT SUM(Amount) AS SalesTotal
WHERE Region = 'West'
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:
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.