18 Oracle CQL Queries, Views, and Joins

18.2.1.1 Select, From, Where Block

Use the sfw_block to specify the select, from, and optional where clauses of your Oracle CQL query.

sfw_block::=

The sfw_block is made up of the following parts:

• Section 18.2.1.2, "Select Clause"

• Section 18.2.1.3, "From Clause"

• Section 18.2.1.4, "Where Clause"

• Section 18.2.1.5, "Group By Clause"

• Section 18.2.1.6, "Order By Clause"

• Section 18.2.1.7, "Having Clause"

18.2.1.2 Select Clause

Use this clause to specify the stream elements you want in the query's result set. The select_clause may specify all stream elements using the * operator or a list of one or more stream elements.

select_clause::=

The list of expressions that appears after the SELECT keyword and before the from_clause is called the select list. Within the select list, you specify one or more stream elements in the set of elements you want Oracle CEP to return from one or more streams or views. The number of stream elements, and their datatype and length, are determined by the elements of the select list.

Optionally, specify distinct if you want Oracle CEP 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.

For more information, see select_clause::=

18.2.1.3 From Clause

Use this clause to specify the streams and views that provide the stream elements you specify in the select_clause (see Section 18.2.1.2, "Select Clause").

The from_clause may specify one or more comma-delimited relation_variable clauses.

from_clause::=

For more information, see from_clause::=

relation_variable::=

You can select from any of the data sources that your relation_variable clause specifies.

You can use the relation_variable clause AS operator to define an alias to label the immediately preceding expression in the select list so that you can reference the result by that (see Section 2.8.1.1, "Aliases in the relation_variable Clause").

If you create a join (see Section 18.4, "Joins") between two or more streams, view, or relations that have some stream element names in common, then you must qualify stream element names with the name of their stream, view, or relation. Example 18-2 shows how to use stream names to distinguish between the customerID stream element in the OrderStream and the customerID stream element in the CustomerStream.

<query id="q0"><![CDATA[ 
    select * from OrderStream, CustomerStream 
    where 
        OrderStream.customerID = CustomerStream.customerID
]]></query>

Otherwise, fully qualified stream element names are optional. However, Oracle recommends that you always qualify stream element references explicitly. Oracle CEP often does less work with fully qualified stream element names.

For more information, see:

• Section 18.2.4, "MATCH_RECOGNIZE Query"

• Section 18.2.6, "XMLTable Query"

• Section 18.2.7, "Function TABLE Query"

• relation_variable::=

18.2.1.4 Where Clause

Use this optional clause to specify conditions that determine when the select_clause returns results (see Section 18.2.1.2, "Select 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:

• opt_where_clause::=

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

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

18.2.1.5 Group By Clause

Use this optional clause to group (partition) results. This clause 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:

• opt_group_by_clause::=

• Section 18.2.1.5, "Group By Clause"

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

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

18.2.1.6 Order By Clause

Use this optional clause to order all results or the top-n results.

For more information, see:

• order_by_clause::=

• order_by_top_clause::=

• Section 18.2.9, "Sorting Query Results"

18.2.1.7 Having Clause

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

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:

• opt_having_clause::=

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

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

18.2.1.8 Binary Clause

Use the binary clause to perform set operations on the tuples that two queries or views return, including:

• EXCEPT

• MINUS

• INTERSECT

• UNION and UNION ALL

• IN and NOT IN

For more information, see binary::=.

18.2.1.9 IDStream Clause

Use this clause to take either a select-from-where clause or binary clause and return its results as one of IStream or DStream relation-to-stream operators.

You can succinctly detect differences in query output by combining an IStream or Dstream operator with the using_clause.

For more information, see:

• idstream_clause::=

• "IStream Relation-to-Stream Operator"

• "DStream Relation-to-Stream Operator"

• Section 18.2.10, "Detecting Differences in Query Results"

18.2.11.1 Parameterized Queries in Oracle CQL Statements

You may specify a placeholder anywhere an arithmetic expresion or a String literal is legal in an Oracle CQL statement. For example:

• SELECT list items

• WHERE clause predicates

• WINDOW constructs (such as RANGE, SLIDE, ROWS, and PARTITION BY)

• PATTERN duration clause

For more information, see:

• "arith_expr"

• "Literals"

18.2.11.2 The bindings Element

Parameter values are contained by a bindings element. There may be one bindings element per processor element.

For each parameterized query, the bindings element must contain a binding element with the same id as the query.

The binding element must contain one or more params elements. Each params element must have a unique id and must contain a comma separated list of parameter values equal in number to the number of placeholder characters (:n) in the corresponding query.

The order of the parameter values corresponds to placeholder characters (:n) in the parameterized query, such that :1 corresponds to the first parameter value, :2 corresponds to the second parameter value, and so on. You may use placeholder characters (:n) in any order. That is, :1 corresponds to the first parameter value whether it precedes or follows :2 in a query. A placeholder number can be used only once in a query.

For more information, see:

• Section 18.2.11.4, "Lexical Conventions for Parameter Values"

• Section 18.2.11.5, "Parameterized Queries at Runtime"

18.2.11.3 Run-Time Query Naming

When a binding instantiates a parameterized query, Oracle CEP creates a new query at run time with the name queryId_paramId. For example, in Example 18-11, the run-time name of the first query instantiated by the MarketRule binding is MarketRule_nasORCL.

To avoid run-time naming conflicts, be sure query ID and parameter ID combinations are unique.

18.2.11.4 Lexical Conventions for Parameter Values

Each params element must have a unique id and must contain a comma separated list of parameter values equal in number to the number of placeholder characters (:n) in the corresponding query.

<params id="p1">NASDAQ, 200.0</params>

<params id="p1">count(*), avg(val)</params>

<params id="p1">'alert', "Seattle, WA", 'fun' || "house", one "two" 3</params>

In an Oracle CQL query, a placeholder within single or double quotes is a String literal. The following query is not a parameterized query:

SELECT ":1" as symbol, price FROM StockTick [RANGE 5 SECONDS]

Oracle CEP parses this query as assigning the String literal ":1" to alias symbol. To make this query into a parameterized query, use:

SELECT :1 as symbol, price FROM StockTick [RANGE :2 SECONDS]

And define a params element like this:

<params id="p1">"ORCL", 5</params>

Because the parameter value (ORCL) does not contain a comma, the quotes are not required. You could specify a params element like this:

<params id="p1">ORCL, 5</params>

However, if the parameter value does contain a comma, then you must use quotes around the parameter value. Consider this parameterized query:

SELECT :1 = cityAndState AS cityOfInterest FROM channel1 [RANGE :2 SECONDS]

Where cityAndState has values like "Seattle, WA" or "Ottawa, ON". In this case, you must specify a params element like this:

<params id="p1">"Seattle, WA", 5</params>
<params id="p1">"Ottawa, ON", 5</params>

Commas are allowed only in quoted parameter values that signify string values. Commas are not allowed as a separator character in unquoted parameter values. For example:

"Seattle, WA" is valid, because the comma is part of the string.

PARTITION BY fromRate,toRate ROWS 10 is invalid. Create the following two parameters instead:

PARTITION BY fromRate ROWS 10
PARTITION BY toRate ROWS 10

18.2.11.5 Parameterized Queries at Runtime

Each params element effectively causes a new Oracle CQL query to execute with the new parameters. At rule execution time, Oracle CQL substitutes parameter values for placeholder characters, from left to right. Example 18-11 is effectively equivalent to the queries that Example 18-12 shows.

SELECT symbol, AVG(price) AS average, NASDAQ AS market
FROM StockTick [RANGE 5 SECONDS]
WHERE symbol = ORCL

SELECT symbol, AVG(price) AS average, NYSE AS market
FROM StockTick [RANGE 5 SECONDS]
WHERE symbol = JPM

SELECT symbol, AVG(price) AS average, NYSE AS market
FROM StockTick [RANGE 5 SECONDS]
WHERE symbol = WFC

18.2.11.6 Replacing Parameters Programmatically

If you use the CQLProcessorMBean.replaceAllBoundParameters() method to programmatically replace parameters in a parameterized query, any existing parameters not replaced by the method are automatically removed from the query.

18.4.2.1 Left Outer Join

To write a query that performs an outer join of streams A and B and returns all stream elements from A (a left outer join), use the LEFT OUTER JOIN syntax in the FROM clause as Example 18-19 shows. For all stream elements in A that have no matching stream elements in B, Oracle CEP returns null for any select list expressions containing stream elements of B.

<query id="q5"><![CDATA[ 
    SELECT a.c1+b.c1 
    FROM S0[range 5] AS a LEFT OUTER JOIN S1[range 3] AS b  ON b.c2 = a.c2
    WHERE b.c2 > 3
]]></query>

18.4.2.2 Right Outer Join

To write a query that performs an outer join of streams A and B and returns all stream elements from B (a right outer join), use the RIGHT OUTER JOIN syntax in the FROM clause as Example 18-20 shows. For all stream elements in B that have no matching stream elements in A, Oracle CEP returns null for any select list expressions containing stream elements of A.

<query id="q5"><![CDATA[ 
    SELECT a.c1+b.c1 
    FROM S0[range 5] AS a RIGHT OUTER JOIN S1[range 3] AS b ON b.c2 = a.c2
    WHERE b.c2 > 3
]]></query>

18.4.2.3 Outer Join Look-Back

You can create an outer join that refers or looks-back to a previous outer join as Example 18-21 shows.

<query id="q5"><![CDATA[ 
    SELECT R1.c1+R2.c1 
    FROM S0[rows 2] as R1 LEFT OUTER JOIN S1[rows 2] as R2 on R1.c2 = R2.c2 RIGHT OUTER JOIN S2[rows 2] as R3 on R2.c2 = R3.c22
    WHERE R2.c2 > 3
]]></query>

18.5.1.1 Cache Key First and Simple Equality

The complex predicate's first subclause (from the left) with a comparison operation over a cache key attribute may only be a simple equality predicate.

The following predicate is invalid because the predicate is not the first sub-clause (from the left) which refers to cache attributes:

... S.c1 = 5 AND CACHE.C2 = S.C2 AND CACHE.C1 = S.C1 ...

However, the following predicate is valid:

... S.c1 = 5 AND CACHE.C1 = S.C1 AND CACHE.C2 = S.C2 ...

18.5.1.2 No Arithmetic Operations on Cache Keys

The subclause may not have any arithmetic operations on a cache key attribute.

The following predicate is invalid because arithmetic operations are not allowed on cache key attributes:

... CACHE.C1 + 5 = S.C1 AND CACHE.C2 = S.C2 ...

18.5.1.3 No Full Scans

The complex predicate must not require a full scan of the cache.

Assume that your cache has cache key C1.

The following predicates are invalid. Because they do not use the cache key attribute in comparisons, they must scan through the whole cache which is not allowed.

... CACHE.C2 = S.C1 ...

... CACHE.C2 > S.C1 ...

... S.C1 = S.C2 ...

... S.C1 = CACHE.C2 AND S.C2 = CACHE.C2 ...

The following predicates are also invalid. Although they do use the cache key attribute in comparisons, they use inequality operations that must scan through the whole cache which is not allowed.

... CACHE.C1 != S.C1 ...

... CACHE.C1 > 5 ...

... CACHE.C1 + 5 = S.C1 ...

The following predicate is also invalid. Although they do use the cache key attribute in comparisons, the first subclause referring to the cache attributes does not refer to the cache key attribute (in this example, C1). That is, the first subclause refers to C2 which is not a cache key and the cache key comparison subclause (CACHE.C1 = S.C1) appears after the non-key comparison subclause.

... CACHE.C2 = S.C2 AND CACHE.C1 = S.C1 ...

18.5.1.4 Multiple Conditions and Inequality

To support multiple conditions, inequality, or both, you must make the first sub-clause an equality predicate comparing a cache key value and specify the rest of the predicate subclauses separated by one AND operator.

The following are valid predicates:

... S.c1 = 5 AND CACHE.C1 = S.C1 AND CACHE.C2 > S.C2 ...

... CACHE.C1 = S.C1 AND CACHE.C2 = S.C2 ...

... S.c1 = 5 AND CACHE.C1 = S.C1 AND CACHE.C2 != S.C2 ...