SELECT Statement

SELECT target_list_entry [, target_list_entry...]
  FROM event_source [...] |
  FROM PATTERN template [pattern_operator template ...] 
  WITHIN (interval TIME | value ON field)
  [FOREACH listfield AS field_identifier_foreach]
  [WHERE predicate]
  [HAVING predicate]
  [GROUP BY field_identifier_grouping [, ...]]
  [ORDER BY field_identifier_ordering [, ...] [DESC] [LIMIT number]]
  [INTO stream_identifier]
  [ERROR INTO stream_identifier]

SELECT is used to retrieve events from an unwindowed stream, one or two windowed streams, a materialized window, or a table. A SELECT statement includes required subclauses. There are two forms of the FROM clause: the first identifies the streams, materialized window or table from which the events are extracted. The second form (FROM PATTERN) identifies one or more streams in which events satisfy a specified pattern. The optional WHERE subclause adds additional restrictions to the SELECT result, such as a range of values or a limit to the number of events received. A SELECT statement can also include nested SELECT statements (also called subqueries).

A target_list_entry is a rule that expands to a value that will be included in each row of the result set. An entry can be extracted from an event present on a stream, from an event in a materialized window, from a row in a table, or from the return from a StreamBase function or expression. You can use wildcards in expressions. If a function or expression is used to generate a value, a field_alias must be specified; if a wildcard expression is used, a wildcard target must be specified.

If the target list includes values derived from aggregate functions, the FROM clause must specify a windowed stream, materialized window, or a table. If the target list of a SELECT statement run against a windowed stream includes event field values, simple function values or scalar expressions, and values derived from aggregate functions, each of the non-aggregate values must be listed in the GROUP BY clause.

When a target_list_entry is an aggregate function applied to a window definition, for example, the openval function, the syntax varies depending on whether the window was defined in a separate CREATE WINDOW statement or listed with the stream identifier in the FROM clause. If a CREATE WINDOW statement is used, then the window identifier is included as a parameter: openval(window_identifier). If the window specification is included within the FROM clause, it does not have a window identifier. In this case, a parameter is not required to invoke the openval() function.

When you use the first (non-PATTERN) form of the FROM clause to extract values from an unwindowed stream, the FROM clause includes only one unwindowed stream event source. If the event source is a windowed stream, the FROM clause includes a stream and window specification. To access a materialized window, the FROM clause must include a stream event source and a materialized window event source, and when accessing a table, the FROM clause must include a stream event source and a table event source. Finally, when performing an event based join, the FROM clause must include two windowed streams.

With a materialized window or table read, one or more field values from each event on the incoming stream are used to select rows from the window or table. Consequently, the SELECT statement can return a result set containing content from multiple window or table entries.

The FROM PATTERN clause must also contain a WITHIN subclause. This subclause defines a window within which the pattern is to be detected, so that the operation is finite and guaranteed to terminate. The window can be either time-based or value-based:

If the WHERE clause is omitted from the SELECT statement, all events or rows are selected from the stream, materialized window, or table.

The HAVING clause is only valid when a SELECT statement is run against a windowed stream.

The ORDER BY field_identifier entries are the stream fields or table columns to use in ordering the result set. The optional LIMIT clause restricts the size of the result set. Note that the LIMIT clause must be used in conjunction with the ORDER BY clause.

Use the FOREACH clause to iterate over the elements of a list field, outputting one tuple for each element in listfield. When using FOREACH, you must specify listfield, a field of type list in the incoming tuple, and must specify a name for a field, field_identifier_foreach, that will be prepended to each outgoing tuple. The incoming tuple is resolved into multiple outgoing tuples, one per element of listfield. The schema of each outgoing tuple is the same as the incoming tuple, but with field_identifier_foreach prepended. The prepended field has the same data type as the list element type of listfield. The prepended field is populated with each element of listfield, in list order, one per outgoing tuple.

The following example outputs one tuple for each element of field prices, which has data type list(double). This statement sends to stream OutStream one tuple for each element in the prices field, with an extra field, eachprice, prepended.

SELECT * FROM InStream
  FOREACH prices AS eachprice
  INTO OutStream;

The return from a SELECT statement must be captured into a stream. The CREATE STREAM Statement statement (or possibly a CREATE OUTPUT STREAM Statement statement) can be combined with an embedded SELECT statement or written as separate statements, as the following fragments illustrate.

CREATE STREAM stream_idenfifier AS
  SELECT ...;

Or

CREATE STREAM stream_identifier;
SELECT ... INTO stream_identifier;

Back to Top ^