WHERE clause

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>

The WHERE clause must appear immediately after the FROM clause.

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. Thus, this example:

RETURN results AS
SELECT
  FactSales_RecordSpec AS id,
  FactSales_ProductKey AS keys
FROM SaleState
WHERE id > 5
ORDER BY keys

is invalid and returns the error message:

In statement "results": In WHERE clause: The state "Sales" does not have an attribute named "id"

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
FROM SaleState
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
FROM SaleState
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.