14 Oracle CQL Queries, Views, and Joins

14.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 14.2.1.2, "Select Clause"

• Section 14.2.1.3, "From Clause"

• Section 14.2.1.4, "Where Clause"

• Section 14.2.1.5, "Group By Clause"

• Section 14.2.1.6, "Order By Clause"

• Section 14.2.1.7, "Having Clause"

14.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::=

14.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 14.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, "Aliases in the relation_variable Clause").

If you create a join (see Section 14.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 14-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.

14.2.1.4 Where Clause

Use this optional clause to specify conditions that determine when the select_clause returns results (see Section 14.2.1.2, "Select Clause").

For more information, see opt_where_clause::=.

14.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.

For more information, see:

• opt_group_by_clause::=

• Section 14.2.1.5, "Group By Clause"

14.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 14.2.6, "Sorting Query Results"

14.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.

For more information, see opt_having_clause::=.

14.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::=.

14.2.1.9 Xstream Clause

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

For more information, see:

• xstream_clause::=

• "IStream Relation-to-Stream Operator"

• "DStream Relation-to-Stream Operator"

• "RStream Relation-to-Stream Operator"

14.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), apply the outer join operator (+) to all stream elements of B in the join condition in the WHERE clause. 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.

14.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), apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. 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.

14.4.2.3 Outer Join Restrictions

Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions:

• The (+) operator can appear only in the WHERE clause and can be applied only to an element of a stream or view.

• If A and B are joined by multiple join conditions, then you must use the (+) operator in all of these conditions. If you do not, then Oracle CEP will return only the stream elements resulting from a simple join, but without a warning or error to advise you that you do not have the results of an outer join.

• The (+) operator can be applied only to a stream element, not to an arbitrary expression. However, an arbitrary expression can contain one or more stream elements marked with the (+) operator.

• You cannot use the (+) operator to outer-join a stream to itself, although self joins are valid.

  For example, the following statement is not valid:

  <query id="q1"><![CDATA[ 
      SELECT
          employee_id, 
          manager_id     FROM
          S0[range 5] as employees
      WHERE 
          employees.manager_id(+) = employees.employee_id
  ]]></query>
  

  However, the following self join is valid:

  <query id="q1"><![CDATA[ 
      SELECT 
          e1.employee_id, 
          e1.manager_id, 
          e2.employee_id    FROM 
          S0[range 5] as e1, 
          S0[range 5] as e2    WHERE 
          e1.manager_id(+) = e2.employee_id
  ]]></query>
  

• A WHERE condition containing the (+) operator cannot be combined with another condition using the OR logical operator.

• A WHERE condition cannot use the IN comparison condition to compare a stream element marked with the (+) operator with an expression.

If the WHERE clause contains a condition that compares a stream element from stream B with a constant, then the (+) operator must be applied to the stream element so that Oracle CEP returns the stream elements from stream A for which it has generated nulls for this stream element. Otherwise Oracle CEP returns only the results of a simple join.

In a query that performs outer joins of more than two pairs of streams, a single stream can be the null-generated stream for only one other stream. For this reason, you cannot apply the (+) operator to stream elements of B in the join condition for A and B and the join condition for B and C.