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:

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.