WHERE clause

Syntax

WHERE condition

Usage

WHERE is a versatile clause, used for filtering rows in several CCL statements, with similar syntax, but slightly different usage and context in each case.

AS SELECTION CONDITION

The first syntactical form of this clause is used optionally, in conjunction with single-source FROM clauses, as well as the FROM Clause: Join Syntax and FROM Clause: Database and Remote Subquery Syntax versions of the FROM clause. The Boolean expression specified in this clause creates a selection that filters rows arriving in the query's data sources before passing them on to the SELECT clause.

• The selection condition can include literals, column references from the query's data sources listed in the FROM clause, operators, scalar and miscellaneous functions, subqueries, parameters, and parentheses. Subqueries in the WHERE clause are discussed later.

• In a Query statement, Database statement, or Remote Procedure statement, column references within the selection condition must refer to columns in one of the query's data sources. In a Delete statement, column references must refer either to columns in a named window or stream referenced in the ON clause, and/or a named window in the DELETE FROM clause.

• When used in conjunction with a MATCHING clause, the WHERE clause can place selection conditions on streams in which events do not occur, (streams that are specified as non-events using the !stream-name syntax in the MATCHING clause).

  Note that, because non-events track the failure of a row to arrive in a stream, the WHERE clause qualifier on a non-event specification expands rather than limits pattern sequences that are considered to have met the matching criteria. For example, a pattern specification of !A, B requires that no row at all arrive in stream A prior to the arrival of a row in stream B within the appropriate interval, whereas the same pattern specification with a WHERE clause of WHERE A >= 10 permits rows with values smaller than 10 to arrive in stream A.

• WHERE clause filtering is performed before the GROUP BY clause and before aggregation (if any), so cannot include aggregate functions or the filtering of results based on the results of aggregates. Use the HAVING clause for post-aggregate filtering.

AS UPDATE CONDITION

When used in an Update Window statement, the expression in the WHERE clause specifies the criteria for updating existing rows in the named window specified in the UPDATE clause, with the values specified in the SET clause, when the update trigger occurs. If none of the rows in the window match the update criteria, new rows are inserted into the window, as indicated with the OTHERWISE INSERT clause, if one is present.

The WHERE clause is optional. In the absence of a WHERE clause, all rows in the window are updated when the trigger occurs. If no trigger for an update occurs, no rows are updated or inserted, whether or not a WHERE clause is specified.

• The update condition expression can include literals, column references, operators, scalar and miscellaneous functions, parameters, and parentheses.

• Column references within the update condition must refer to one of the columns in the ON clause, or to the named window in the UPDATE clause.

• In the absence of an OTHERWISE INSERT clause, the update condition can be any valid Boolean expression. However, if you use an OTHERWISE INSERT clause in your Update Window statement, the update condition must consist of one or more simple equality comparisons, each of which compare a column in the ON clause stream or window to the named window in the UPDATE clause. When multiple column comparisons are specified, they are separated by the AND keyword.

AS DELETE CONDITION

When used in a Delete statement, the expression in the WHERE clause specifies the criteria for deleting rows from the named window indicated in the statement, once the deletion trigger occurs. This clause is optional. In the absence of a WHERE clause, all rows are deleted from the window when the trigger occurs. If no trigger for deletion occurs, no rows are deleted from the window, whether or not a WHERE clause is specified.

• The delete condition expression can include literals, column references, operators, scalar and miscellaneous functions, subqueries, parameters, and parentheses.

• Column references within the delete condition must refer to one of the columns in the window's schema.

AS JOIN CONDITIONS

When used in conjunction with the FROM Clause: Comma-Separated Syntax form of the FROM clause, the WHERE clause creates one or more join condition for the comma-separated join. The use of a WHERE clause is optional in a comma-separated join. In the absence of a join condition, all rows from all data sources are selected. When a WHERE clause is present, its syntax resembles the ON Clause: Join Syntax (which is not used with comma-separated joins). The join condition can be any valid Boolean expression that specifies the condition for the join. All column references in this form of the WHERE clause must refer to data sources specified with the FROM clause.

CCL Subqueries

The selection condition or delete condition expression (but not the update condition or join condition) of the WHERE clause can include scalar CCL subqueries using the following syntax:

( select_clause from_clause [matching_clause] [on_clause] [where_clause] [group_by_clause] [having_clause] [output_clause] )

The subquery is enclosed in parentheses and follows the same syntax as the Query statement, but without an INSERT clause and without the final semicolon. Subqueries in the WHERE clause are only permitted when they produce scalar output (one row per execution of the query). Aggregate output is not permitted, though aggregate functions can be used in the SELECT and HAVING clauses of the subquery to produce a single line of output from multiple lines. A subquery in the WHERE clause cannot directly reference a data stream or window in the parent or outer query and vice versa. WHERE clause subqueries can be nested.

Restrictions

• A WHERE clause cannot use aggregate functions.

• Subqueries in a WHERE clause expression must produce scalar output.

• Joins using the JOIN keyword do not use the WHERE clause to specify join conditions (though they can use the clause in its selection condition form). See ON Clause: Join Syntax for more information.

• The following restrictions apply to a WHERE clause that specifies a relationship between two or more streams contained in a MATCHING clause pattern specification, when one of the members of the relationship appears in the MATCHING clause as a non-event (using the !stream-name syntax).

  • All the members of the relationship in the WHERE clause must appear in the MATCHING clause in a sequence (comma-separated) list.

  • None of the members of the relationship in the WHERE clause can appear in the MATCHING clause in a sub-pattern or nested bounded pattern relationship to the other members. For example, a where clause of WHERE A = B is allowed with a MATCHING clause pattern specification of MATCHING [5 MINUTES: A, !B, C], but not with a pattern specification of MATCHING [5 MINUTES: A, (!B, C) && D].

See Also

• Query Statement

• Database Statement

• Delete Statement

• Remote Procedure Statement

• FROM Clause: Comma-Separated Syntax

• HAVING

• MATCHING

• ON

• OTHERWISE INSERT

• SET

• WHEN

Examples

The following example uses a subquery in the WHERE clause. The selection condition is used to produce a continuous stream of Boolean values that indicate whether or not a stock price is at least $1.00 above the moving average:

INSERT INTO OutStream
SELECT *
FROM Trades
WHERE Trades.Price > 1.00 + 
   (SELECT AVG(Price) 
    FROM Trades KEEP 100 ROWS);

The following WHERE clause defines a selection condition for a join:

INSERT INTO OutStream
SELECT Trades.Symbol, Trades.Price, Broker.Name
FROM Trades KEEP 1 DAY, Broker KEEP 1 DAY
WHERE Trades.Name = Broker.Name;

The following example updates the Status column of MyWindow for all rows that contain the same symbol as the symbol arriving in the triggering InStream:

ON InStream
UPDATE MyWindow
SET MyWindow.Status = "SOLD"
WHERE InStream.Symbol = MyWindow.Symbol;

The following example specifies that whenever a row with the message "Clear Window" arrives in the stream InStream, rows with the same value in the Symbol column as the arriving row should be deleted from the NamedWin window:

ON InStream
WHEN InStream.Message = 'Clear Window'
DELETE FROM NamedWin
WHERE InStream.Symbol = NamedWin.Symbol;