The WHERE
clause is used to filter input records for an expression.
WHERE
and HAVING
. The syntax of the WHERE
clause is as follows:
WHERE <BooleanExpression>
The WHERE
clause must appear immediately after the FROM
clause.
WHERE
clause with any Boolean expression, such as:
{= , <>, <, <=, >, >=}
SUBSET
and IS_MEMBER_OF
<attribute> IS {NULL, NOT NULL}
(for atomic values) and <attribute> IS {EMPTY, NOT EMPTY}
(for sets)<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.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 keysis 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.
RETURN Reps AS SELECT SUM(Amount) AS SalesTotal FROM SaleState WHERE Region = 'West' GROUP BY SalesRep HAVING SalesTotal > 10000
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).
WHERE
clause:
SUM(Amount) WHERE (SalesRep = 'Juan Smith') AS JuanTotal
For more information on per-aggregate WHERE
filters, see Per-aggregation filters.