named_query

Specify the Oracle CQL query statement itself.

For syntax, see Query.

query

You can create an Oracle CQL query from any of the following clauses:

• sfw_block: a select, from, and other optional clauses.

• binary: an optional clause, often a set operation.

sfw_block

Specify the select, from, and other optional clauses of the Oracle CQL query. You can specify any of the following clauses:

• select_clause: the stream elements to select from the stream or view you specify.

• from_clause: the stream or view from which your query selects.

• opt_where_clause: optional conditions your query applies to its selection

• opt_group_by_clause: optional grouping conditions your query applies to its result

  order_by_clause: optional ordering conditions your query applies to its results

• order_by_top_clause: optional ordering conditions your query applies to the top-n elements in its results

• opt_having_clause: optional clause your query uses to restrict the groups of returned stream elements to those groups for which the specified condition is TRUE

select_clause

Specify the select clause of the Oracle CQL query statement.

If you specify the asterisk (*), then this clause returns all tuples, including duplicates and nulls.

Otherwise, specify the individual stream elements you want.

Optionally, specify distinct if you want Event Processing to return only one copy of each set of duplicate tuples selected. Duplicate tuples are those with matching values for each expression in the select list.

non_mt_projterm_list

Specify the projection term or comma separated list of projection terms in the select clause of the Oracle CQL query statement.

projterm

Specify a projection term in the select clause of the Oracle CQL query statement. You can select any element from any of stream or view in the from_clause using the identifier of the element.

Optionally, you can specify an arithmetic expression on the projection term.

Optionally, use the AS keyword to specify an alias for the projection term instead of using the stream element name as is.

from_clause

Specify the from clause of the Oracle CQL query statement by specifying the individual streams or views from which your query selects.

To perform an outer join, use the LEFT or RIGHT OUTER JOIN ... ON syntax. To perform an inner join, use the WHERE clause.

non_mt_relation_list

Specify the stream in the from clause of the Oracle CQL query statement.

relation_variable

Use the relation_variable statement to specify a stream or view from which the Oracle CQL query statement selects.

You can specify a previously registered or created stream or view directly by its identifier you used when you registered or created the stream or view. Optionally, use the AS keyword to specify an alias for the stream or view instead of using its name as is.

To specify a built-in stream-to-relation operator, use a window_type clause. Optionally, use the AS keyword to specify an alias for the stream or view instead of using its name as is.

To apply advanced comparisons optimized for data streams to the stream or view, use a pattern_recognition_clause . Optionally, use the AS keyword to specify an alias for the stream or view instead of using its name as is.

window_type

Specify a built-in stream-to-relation operator.

For more information, see Stream-to-Relation Operators (Windows).

time_spec

Specify the time over which a range or partitioned range sliding window should slide.

Default: if units are not specified, Oracle Event Processing assumes [second|seconds].

For more information, see Range-Based Stream-to-Relation Window Operators and Partitioned Stream-to-Relation Window Operators.

opt_where_clause

Specify the (optional) where clause of the Oracle CQL query statement.

Because Oracle CQL applies the WHERE clause before GROUP BY or HAVING, if you specify an aggregate function in the SELECT clause, you must test the aggregate function result in a HAVING clause, not the WHERE clause.

In Oracle CQL (as in SQL), the FROM clause is evaluated before the WHERE clause. Consider the following Oracle CQL query:

SELECT ... FROM S MATCH_RECOGNIZE ( .... ) as T WHERE ...

In this query, the S MATCH_RECOGNIZE ( .... ) as T is like a subquery in the FROM clause and is evaluated first, before the WHERE clause. Consequently, you rarely use both a MATCH_RECOGNIZE clause and a WHERE clause in the same Oracle CQL query. Instead, you typically use views to apply the required WHERE clause to a stream and then select from the views in a query that applies the MATCH_RECOGNIZE clause.

For more information, see:

• Built-In Aggregate Functions and the Where, Group By, and Having Clauses

• Colt Aggregate Functions and the Where, Group By, and Having Clauses

• MATCH_RECOGNIZE and the WHERE Clause.

opt_group_by_clause

Specify the (optional) GROUP BY clause of the Oracle CQL query statement. Use the GROUP BY clause if you want Oracle Event Processing to group the selected stream elements based on the value of expr(s) and return a single (aggregate) summary result for each group.

Expressions in the GROUP BY clause can contain any stream elements or views in the FROM clause, regardless of whether the stream elements appear in the select list.

The GROUP BY clause groups stream elements but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause.

Because Oracle CQL applies the WHERE clause before GROUP BY or HAVING, if you specify an aggregate function in the SELECT clause, you must test the aggregate function result in a HAVING clause, not the WHERE clause.

For more information, see:

• Built-In Aggregate Functions and the Where, Group By, and Having Clauses

• Colt Aggregate Functions and the Where, Group By, and Having Clauses.

order_by_clause

Specify the ORDER BY clause of the Oracle CQL query statement as a comma-delimited list of one or more order terms. Use the ORDER BY clause to specify the order in which stream elements on the left-hand side of the rule are to be evaluated. The expr must resolve to a dimension or measure column. This clause returns a stream.

Both ORDER BY and ORDER BY ROWS support specifying the direction of sort as ascending or descending by using the ASC or DESC keywords. They also support specifying whether null items should be listed first or last when sorting by using NULLS FIRST or NULLS LAST.

order_by_top_clause

Specify the ORDER BY clause of the Oracle CQL query statement as a comma-delimited list of one or more order terms followed by a ROWS keyword and integer number (n) of elements. Use this form of the ORDER BY clause to select the top-n elements over a stream or relation. This clause returns a relation.

Consider the following example queries:

• At any point of time, the output of the following example query will be a relation having top 10 stock symbols throughout the stream.

  select stock_symbols from StockQuotes order by stock_price rows 10
  

• At any point of time, the output of the following example query will be a relation having top 10 stock symbols from last 1 hour of data.

  select stock_symbols from StockQuotes[range 1 hour] order by stock_price rows 10
  

order_by_list

Specify a comma-delimited list of one ore more order terms in an (optional) ORDER BY clause.

orderterm

A stream element or positional index (constant int) to a stream element. Optionally, you can configure whether or not nulls are ordered first or last using the NULLS keyword.

order_expr

order_expr can be an attr or constant_int. The attr can be any stream element or pseudo column.

null_spec

Specify whether or not nulls are ordered first (NULLS FIRST) or last (NULLS LAST) for a given order term.

asc_desc

Specify whether an order term is ordered in ascending (ASC) or descending (DESC) order.

opt_having_clause

Use the HAVING clause to restrict the groups of returned stream elements to those groups for which the specified condition is TRUE. If you omit this clause, GGSA returns summary results for all groups.

Specify GROUP BY and HAVING after the opt_where_clause. If you specify both GROUP BY and HAVING, then they can appear in either order.

Because Oracle CQL applies the WHERE clause before GROUP BY or HAVING, if you specify an aggregate function in the SELECT clause, you must test the aggregate function result in a HAVING clause, not the WHERE clause.

For more information, see:

• Built-In Aggregate Functions and the Where, Group By, and Having Clauses

• Colt Aggregate Functions and the Where, Group By, and Having Clauses.

binary

Use the binary clause to perform operations on the tuples that two streams or views return. Most of these perform set operations, receiving two relations as operands. However, the UNION ALL operator can instead receive two streams, which are by nature unbounded.

idstream_clause

Use an idstream_clause to specify an IStream or DStream relation-to-stream operator that applies to the query.

using_clause

Use a DIFFERENCE USING clause to succinctly detect differences in the IStream or DStream of a query.

usinglist

Use a usinglist clause to specify the columns to use to detect differences in the IStream or DStream of a query. You may specify columns by:

• attribute name: use this option when you are selecting by attribute name.

• alias: use this option when you want to include the results of an expression where an alias is specified.

• position: use this option when you want to include the results of an expression where no alias is specified.

  Specify position as a constant, positive integer starting at 1, reading from left to right.

  The following example specifies the result of expression funct(c2, c3) by its position (3) in the DIFFERENCE USING clause usinglist.

<query id="q1">
    ISTREAM (
        SELECT c1, log(c4) as logval, funct(c2, c3) FROM S [RANGE 1 NANOSECONDS]
    )    DIFFERENCE USING (c1, logval, 3)
</query>

pattern_recognition_clause

Use a pattern_recognition_clause to perform advanced comparisons optimized for data streams.

For more information and examples, see Pattern Recognition With MATCH_RECOGNIZE.

Simple Query Example

The following example shows how to register a simple query q0 that selects all (*) tuples from stream OrderStream where stream element orderAmount is greater than 10000.

<query id="q0"><![CDATA[ 
    select * from OrderStream where orderAmount > 10000.0 
]]></query>

HAVING Example

Consider the query q4 and the data stream S2. Stream S2 has schema (c1 integer, c2 integer). The query returns the relation.

<query id="q4"><![CDATA[ 
    select 
        c1,
        sum(c1) 
    from 
        S2[range 10] 
    group by 
        c1 
    having 
        c1 > 0 and sum(c1) > 1
]]></query>

Timestamp   Tuple
1000        ,2
2000        ,4
3000        1,4
5000        1,
6000        1,6
7000        ,9
8000        ,

Timestamp   Tuple Kind  Tuple
5000:       +           1,2
6000:       -           1,2
6000:       +           1,3

ORDER BY Query Example

Use the ORDER BY clause with stream input to sort events that have duplicate timestamps. ORDER BY is only valid when the input is a stream and only sorts among events of the same timestamp. Its output is a stream with the sorted events.

Consider the query q1. Given the data stream S0, the query returns the relation. The query sorts events of duplicate timestamps in ascending order by tuple values.

<query id="q1"><![CDATA[ 
    SELECT * 
    FROM S0 
    ORDER BY c1,c2 ASC
]]></query>

Timestamp   Tuple
1000        7, 15
2000        7, 14
2000        5, 23
2000        5, 15
2000        5, 15
2000        5, 25
3000        3, 12
3000        2, 13
4000        4, 17
5000        1, 9
h 1000000000

Timestamp   Tuple Kind  Tuple
1000:       +           7,15
2000:       +           5,15
2000:       +           5,15
2000:       +           5,23
2000:       +           5,25
3000:       +           2,13
3000:       +           3,19
4000:       +           4,17
5000:       +           1,9

ORDER BY ROWS Query Example

Use the ORDER BY clause with the ROWS keyword to use ordering criteria to determine whether an event received by the query should be included in output. ORDER BY ROWS accepts either stream or relation input and outputs a relation.

The ORDER BY ROWS clause maintains a set of events whose maximum size is the number specified by the ROWS keyword. As new events are received, they are evaluated, based on thr order criteria and the ROWS limit, to determine whether they will be added to the output.

Note that the output of ORDER BY ROWS is not arranged based on the ordering criteria, as is the output of the ORDER BY clause. Instead, ORDER BY ROWS uses the ordering criteria and specified number of rows to determine whether to admit events into the output as they are received.

Consider the query q1. Given the data stream S0, the query returns the relation.

<query id="q1"><![CDATA[ 
    SELECT c1 ,c2 
    FROM S0 
    ORDER BY c1,c2 ROWS 5
]]></query>

Timestamp   Tuple
1000        7, 15
2000        7, 14
2000        5, 23
2000        5, 15
2000        5, 15
2000        5, 25
3000        2, 13
3000        3, 19
4000        4, 17
5000        1, 9
h 1000000000

Timestamp   Tuple Kind  Tuple
1000:       +           7,15
2000:       +           7,14
2000:       +           5,23
2000:       +           5,15
2000:       +           5,15
2000:       -           7,15
2000:       +           5,25
3000:       -           7,14
3000:       +           2,13
3000:       -           5,25
3000:       +           3,19
4000:       -           5,23
4000:       +           4,17
5000:       -           5,15
5000:       +           1,9

In the following example, the query uses the PARTITION keyword to specify the tuple property within which to sort events and constrain output size. Here, the PARTITION keyword specifies that events in the input should be evaluated based on their symbol value.

In other words, when determining whether to include an event in the output, the query looks at the existing set of events in output that have the same symbol. The ROWS limit is two, meaning that the query will maintain a set of sorted events that has no more than two events in it. For example, if there are already two events with the ORCL symbol, adding another ORCL event to the output will require deleting the oldest element in output having the ORCL symbol.

Also, the query is ordering events by the value property, so that is also considered when a new event is being considered for output. Here, the DESC keyword specifies that event be ordered in descending order. A new event that does not come after events already in the output set will not be included in output.

<query id="q1"><![CDATA[ 
    SELECT symbol, value
    FROM S0 
    ORDER BY value DESC ROWS 2 
    PARTITION BY symbol
]]></query>

Timestamp   Tuple
1000        ORCL, 500
1100        MSFT, 400
1200        INFY, 200
1300        ORCL, 503
1400        ORCL, 509
1500        ORCL, 502
1600        MSFT, 405
1700        INFY, 212
1800        INFY, 209
1900        ORCL, 512
2000        ORCL, 499
2100        MSFT, 404
2200        MSFT, 403
2300        INFY, 215
2400        MSFT, 415
2500        ORCL, 499
2600        INFY, 211

Timestamp    Tuple Kind    Tuple
1000         +             ORCL,500
1100         +             MSFT,400
1200         +             INFY,200
1300         +             ORCL,503
1400         -             ORCL,500
1400         +             ORCL,509
1600         +             MSFT,405
1700         +             INFY,212
1800         -             INFY,200
1800         +             INFY,209
1900         -             ORCL,503
1900         +             ORCL,512
2100         -             MSFT,400
2100         +             MSFT,404
2300         -             INFY,209
2300         +             INFY,215
2400         -             MSFT,404
2400         +             MSFT,415