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:
{= , <>, <, <=, >, >=}
- Null value evaluation:
<attribute> IS {NULL, NOT NULL}
- 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.
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).