WHERE Clause

Specifies a selection condition, join condition, update condition, or delete condition to filter rows of data.

Syntax

WHERE condition | filterexpression

Components

condition	A Boolean expression representing a selection, update, delete, or join condition, depending on the context.
filterexpression	A Boolean expression based on the columns from a stream.

Usage

The WHERE clause filters rows and columns in several CCL statements, with similar syntax, but different usage and context. The WHERE clause:

Specifies a selection condition for filter input from data sources in a QUERY element.
Provides join conditions in a FROM clause.

As a Selection Condition

The WHERE clause acts as a selection condition when used with a FROM clause.

The Boolean expression in this clause creates a selection that filters rows arriving in the query's data sources before passing them on to the SELECT clause. WHERE clause filtering is performed before the GROUP BY clause and before aggregation (if any), so it cannot include aggregate functions or the filtering of results based on the results of aggregates. You can use the HAVING clause for post-aggregate filtering.

The selection condition can include literals, column references from the query's data sources listed in the FROM clause, operators, scalar functions, parameters, and parentheses.

In a query, column references within the selection condition must refer to columns in one of the query's data sources.

As a Join Condition

When used in conjunction with the 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 with ANSI join syntax.

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.

As a Filter Expression

Filter expressions are supported only in input streams.

When using columns in a filter expression, use the nodeName.columnName notation. nodeName is the name of the input stream.

Restrictions

A WHERE clause cannot use aggregate functions.
A WHERE clause cannot be used with a MATCHING clause.
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).

Examples

This example uses a WHERE clause as a select condition:

CREATE INPUT WINDOW QTrades SCHEMA (
	Id integer, 
	TradeTime date,
	Venue string,  
	Symbol string, 
	Price float, 
	Shares integer
)
PRIMARY KEY (Id);

CREATE OUTPUT WINDOW QTradesComputeSelected
PRIMARY KEY DEDUCED
AS SELECT
	trd.*
FROM
	QTrades trd
WHERE
	trd.Symbol IN ('DELL','CSCO','SAP')
;

This example uses a WHERE clause as a join condition:

CREATE INPUT WINDOW QTrades SCHEMA (
	Id integer, 
	TradeTime date,
	Venue string,  
	Symbol string, 
	Price float, 
	Shares integer
)
PRIMARY KEY (Id);

CREATE OUTPUT WINDOW RecentQTrades
PRIMARY KEY DEDUCED
AS
    SELECT q.Symbol, nth(0, q.Price) Price, nth(0, q.Shares) Shares
FROM
    QTrades q
GROUP BY q.Symbol
GROUP ORDER BY q.ROWID DESC
;

CREATE INPUT WINDOW Positions
SCHEMA (BookId STRING, Symbol STRING, SharesHeld INTEGER)
PRIMARY KEY (BookId, Symbol)
;

CREATE OUTPUT WINDOW PositionValue
PRIMARY KEY (BookId, Symbol)
AS SELECT
	pos.BookId,
	pos.Symbol,
	pos.SharesHeld,
	pos.SharesHeld * q.Price Value
FROM
	Positions pos, RecentQTrades q WHERE pos.Symbol = q.Symbol
;

This example uses a WHERE clause as a filter expression:

CREATE INPUT STREAM LSETradesFiltered SCHEMA (
	Id integer, 
	TradeTime date,
	Venue string,  
	Symbol string, 
	Price float, 
	Shares integer
)
WHERE LSETradesFiltered.Symbol IN ('SAP', 'CSCO', 'DELL')
;