Skip Headers
Oracle® CEP CQL Language Reference
11g Release 1 (11.1.1)

Part Number E12048-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

14 Oracle CQL Queries, Views, and Joins

You select, process, and filter element data from streams and relations using Oracle CQL queries and views.

A top-level SELECT statement that you create using the [REGISTER|CREATE] QUERY statement is called a query.

A top-level VIEW statement that you create using the [REGISTER|CREATE] VIEW statement is called a view (the Oracle CQL equivalent of a subquery).

A join is a query that combines rows from two or more streams, views, or relations.

For more information, see:

14.1 Introduction to Oracle CQL Queries, Views, and Joins

An Oracle CQL query is an operation that you express in Oracle CQL syntax and execute on an Oracle CEP CQL Processor to process data from one or more streams or views. For more information, see Section 14.2, "Queries".

An Oracle CQL view represents an alternative selection on a stream or relation. In Oracle CQL, you use a view instead of a subquery. For more information, see Section 14.3, "Views".

Oracle CEP performs a join whenever multiple streams appear in the FROM clause of the query. For more information, see Section 14.4, "Joins".

Example 14-1 shows typical Oracle CQL queries defined in an Oracle CQL processor component configuration file for the processor named proc.

Example 14-1 Typical Oracle CQL Query

<?xml version="1.0" encoding="UTF-8"?>
<n1:config
    xsi:schemaLocation="http://www.bea.com/ns/wlevs/config/application wlevs_application_config.xsd"
    xmlns:n1="http://www.bea.com/ns/wlevs/config/application"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <processor>
        <name>proc</name>
        <rules>
            <rule id="range_slide"><![CDATA[ 
                register window range_slide(winrange int, winslide int) implement using "MyRangeSlideWindow"
            ]]></rule>
            <view id="lastEvents" schema="cusip mbid srcId bidQty ask askQty seq"><![CDATA[ 
                select cusip, mod(bid) as mbid, srcId, bidQty, ask, askQty, seq 
                from filteredStream[partition by srcId, cusip rows 1]
            ]]></view>
            <query id="q1"><![CDATA[
                SELECT *
                FROM   lastEvents [Now]
                WHERE  price > 10000
            ]]></query>
        </rules>
    </processor>
</n1:config>

As Example 14-1 shows, the rules element contains each Oracle CQL statement in a rule, view, or query child element:

Each Oracle CQL statement is contained in a <![CDATA[ ... ]]> tag and does not end in a semicolon (;).

For more information, see:

To create an Oracle CQL query:

  1. Using Oracle CEP IDE for Eclipse, create an Oracle CEP application and Event Processing Network (EPN).

    For more information, see Oracle CEP IDE Developer's Guide for Eclipse.

  2. In the EPN Editor, right-click an Oracle CQL processor and select Go to Configuration Source as Figure 14-1 shows.

    Figure 14-1 Navigating to the Configuration Source of a Processor from the EPN Editor

    Description of Figure 14-1 follows
    Description of "Figure 14-1 Navigating to the Configuration Source of a Processor from the EPN Editor"

    The EPN Editor opens the corresponding component configuration file for this processor and positions the cursor in the appropriate processor element as Figure 14-2 shows.

    Figure 14-2 Editing the Configuration Source for a Processor

    Description of Figure 14-2 follows
    Description of "Figure 14-2 Editing the Configuration Source for a Processor"

  3. Create queries and views and register user-defined functions and windows.

    For examples, see

  4. Using Oracle CEP IDE for Eclipse, package your Oracle CEP application and deploy to the Oracle CEP server.

14.2 Queries

Queries are the principle means of extracting information from data streams and views.

registerquery::=

Surrounding text describes registerquery.gif.

Note:

The important top level query syntax is shown here. Refer to "Query" for the full syntax and semantics of this statement.

The named_query clause itself is made up of a query clause.

query::=

Surrounding text describes query.gif.

The query clause itself is made up of one of the following parts:

The following sections discuss the basic query types that you can create:

14.2.1 Query Building Blocks

This section summarizes the basic building blocks that you use to construct an Oracle CQL query, including:

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

Surrounding text describes sfw_block.gif.

The sfw_block is made up of the following parts:

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

Surrounding text describes select_clause.gif.

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

Surrounding text describes from_clause.gif.

For more information, see from_clause::=

relation_variable::=

Surrounding text describes relation_variable.gif.

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.

Example 14-2 Fully Qualified Stream Element Names

<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:

14.2.1.6 Order By Clause

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

For more information, see:

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:

14.2.2 Simple Query

Example 14-3 shows a simple query that selects all stream elements from a single stream.

Example 14-3 Simple Query

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

For more information, see "Query".

14.2.3 Built-In Window Query

Example 14-4 shows a query that selects all stream elements from stream S2, with schema (c1 integer, c2 float), using a built-in tuple-based stream-to-relation window operator.

Example 14-4 Built-In Window Query

<query id="BBAQuery"><![CDATA[ 
    create query q209 as select * from S2 [range 5 minutes] where S2.c1 > 10
]]></query>

For more information, see:

14.2.4 MATCH_RECOGNIZE Query

Example 14-5 shows a query that uses the MATCH_RECOGNIZE clause to express complex relationships among the stream elements of ItemTempStream.

Example 14-5 MATCH_RECOGNIZE Query

<query id="detectPerish"><![CDATA[ 
  select its.itemId
  from tkrfid_ItemTempStream MATCH_RECOGNIZE (
      PARTITION BY itemId
      MEASURES A.itemId as itemId
      PATTERN (A B* C)
      DEFINE
          A  AS  (A.temp >= 25),
          B  AS  ((B.temp >= 25) and (to_timestamp(B.element_time) - to_timestamp(A.element_time) < INTERVAL "0 00:00:05.00" DAY TO SECOND)),
          C  AS  (to_timestamp(C.element_time) - to_timestamp(A.element_time) >= INTERVAL "0 00:00:05.00" DAY TO SECOND)
  ) as its
]]></query>

For more information, see:

14.2.5 XMLTable Query

Example 14-6 shows a view v1 and a query q1 on that view. The view selects from a stream S1 of xmltype stream elements. The view v1 uses the XMLTABLE clause to parse data from the xmltype stream elements using XPath expressions. Note that the data types in the view's schema match the datatypes of the parsed data in the COLUMNS clause. The query q1 selects from this view as it would from any other data source.

Example 14-6 XMLTABLE Query

<view id="v1" schema="orderId LastShares LastPrice"><![CDATA[ 
    select 
        X.OrderId, 
        X.LastShares, 
        X.LastPrice 
    from 
        S1 
    XMLTABLE (
        "//FILL" 
        PASSING BY VALUE 
            S1.c1 as "." 
        COLUMNS 
            OrderId char(16) PATH "fn:data(../@ID)", 
            LastShares integer PATH "fn:data(@LastShares)", 
            LastPrice float PATH "fn:data(@LastPx)"
    ) as X
]]></view>

<query id="q1"><![CDATA[ 
    IStream(
        select 
            orderId, 
            sum(LastShares * LastPrice), 
            sum(LastShares * LastPrice) / sum(LastShares)
        from 
            v1[now] 
        group by orderId
    )
]]></query>

For more information, see:

14.2.6 Sorting Query Results

Use the ORDER BY clause to order the rows selected by a query.

order_by_clause::=

Surrounding text describes order_by_clause.gif.

(order_by_list::=)

Sorting by position is useful in the following cases:

  • To order by a lengthy select list expression, you can specify its position in the ORDER BY clause rather than duplicate the entire expression.

  • For compound queries containing set operators UNION, INTERSECT, MINUS, or UNION ALL, the ORDER BY clause must specify positions or aliases rather than explicit expressions. Also, the ORDER BY clause can appear only in the last component query. The ORDER BY clause orders all rows returned by the entire compound query.

The mechanism by which Oracle CEP sorts values for the ORDER BY clause is specified by your Java locale.

14.3 Views

Queries are the principle means of extracting information from data streams and relations. A view represents an alternative selection on a stream or relation that you can use to create subqueries.

registerview::=

Surrounding text describes registerview.gif.

view_description::=

Surrounding text describes view_description.gif.

Note:

The important top level query syntax is shown here. Refer to "View" for the full syntax and semantics of this statement.

In Example 14-7, query BBAQuery selects from view MAXBIDMINASK which in turn selects from other views such as BIDMAX which in turn selects from other views. Finally, views such as lastEvents select from an actual event source: filteredStream. Each such view represents a separate derived stream drawn from one or more base streams.

Example 14-7 Using Views Instead of Subqueries

<view id="lastEvents" schema="cusip bid srcId bidQty ask askQty seq"><![CDATA[ 
    select cusip, bid, srcId, bidQty, ask, askQty, seq 
    from filteredStream[partition by srcId, cusip rows 1]
]]></view>
<view id="bidask" schema="cusip bid ask"><![CDATA[ 
    select cusip, max(bid), min(ask) 
    from lastEvents
    group by cusip
]]></view>
<view id="bid" schema="cusip bid seq"><![CDATA[ 
    select ba.cusip as cusip, ba.bid as bid, e.seq 
    from bidask as ba, lastEvents as e
    WHERE e.cusip = ba.cusip AND e.bid = ba.bid
]]></view>
<view id="bid1" schema="cusip maxseq"><![CDATA[ 
    select b.cusip, max(seq) as maxseq
    from bid as b 
    group by b.cusip  
]]></view>
<view id="BIDMAX" schema="cusip seq srcId bid bidQty"><![CDATA[ 
    select e.cusip, e.seq, e.srcId, e.bid, e.bidQty
    from bid1 as b, lastEvents as e
    where (e.seq = b.maxseq) 
]]></view>
<view id="ask" schema="cusip ask seq"><![CDATA[ 
    select ba.cusip as cusip, ba.ask as ask, e.seq
    from bidask as ba, lastEvents as e
    WHERE e.cusip = ba.cusip AND e.ask = ba.ask
]]></view>
<view id="ask1" schema="cusip maxseq"><![CDATA[ 
    select a.cusip, max(seq) as maxseq 
    from ask as a
    group by a.cusip
]]></view>
<view id="ASKMIN" schema="cusip seq srcId ask askQty"><![CDATA[ 
    select e.cusip, e.seq, e.srcId, e.ask, e.askQty 
    from ask1 as a, lastEvents as e 
    where (e.seq = a.maxseq)
]]></view>
<view id="MAXBIDMINASK" schema="cusip bidseq bidSrcId bid askseq askSrcId ask bidQty askQty"><![CDATA[ 
    select bid.cusip, bid.seq, bid.srcId as bidSrcId, bid.bid, ask.seq, ask.srcId as askSrcId, ask.ask, bid.bidQty, ask.askQty 
    from BIDMAX as bid, ASKMIN as ask 
    where bid.cusip = ask.cusip
]]></view>
<query id="BBAQuery"><![CDATA[ 
    ISTREAM(select bba.cusip, bba.bidseq, bba.bidSrcId, bba.bid, bba.askseq, bba.askSrcId, bba.ask, bba.bidQty, bba.askQty, "BBAStrategy" as intermediateStrategy, p.seq as correlationId, 1 as priority 
    from MAXBIDMINASK as bba, filteredStream[rows 1] as p where bba.cusip = p.cusip)
]]></query>

Using this technique, you can achieve the same results as in the subquery case. However, using views you can better control the complexity of queries and reuse views by name in other queries.

If you create a join (see Section 14.4, "Joins") between two or more views that have some stream element names in common, then you must qualify stream element names with names of streams. Example 14-8 shows how to use view names to distinguish between the seq stream element in the BIDMAX view and the seq stream element in the ASKMIN view.

Example 14-8 Using View Names to Distinguish Between Stream Elements of the Same Name

<view id="MAXBIDMINASK" schema="cusip bidseq bidSrcId bid askseq askSrcId ask bidQty askQty"><![CDATA[ 
    select bid.cusip, bid.seq, bid.srcId as bidSrcId, bid.bid, ask.seq, ask.srcId as askSrcId, ask.ask, bid.bidQty, ask.askQty 
    from BIDMAX as bid, ASKMIN as ask 
    where bid.cusip = ask.cusip
]]></view>

Otherwise, fully qualified stream element names are optional. However, it is always a good idea to qualify stream element references explicitly. Oracle CEP often does less work with fully qualified stream element names.

You can specify any query type in the definition of your view. For more information, see Section 14.2, "Queries".

14.4 Joins

A join is a query that combines rows from two or more streams, views, or relations. Oracle CEP performs a join whenever multiple streams appear in the FROM clause of the query. The select list of the query can select any stream elements from any of these streams. If any two of these streams have a stream element name in common, then you must qualify all references to these stream elements throughout the query with stream names to avoid ambiguity.

If you create a join 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-9 shows how to use stream names to distinguish between the customerID stream element in the OrderStream stream and the customerID stream element in the CustomerStream stream.

Example 14-9 Fully Qualified Stream Element Names

<query id="q0"><![CDATA[ 
    select * from OrderStream[range 5] as orders, CustomerStream[range 3] as customers where 
        orders.customerID = customers.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.

Oracle CEP supports the following types of joins:

14.4.1 Inner Joins

By default, Oracle CEP performs an inner join (sometimes called a simple join): a join of two or more streams that returns only those stream elements that satisfy the join condition.

Example 14-10 shows how to create a query q4 that uses an inner join between streams S0, with schema (c1 integer, c2 float), and S1, with schema (c1 integer, c2 float).

Example 14-10 Inner Joins

<query id="q4"><![CDATA[ 
    select * 
        from 
            S0[range 5] as a, 
            S1[range 3] as b 
        where 
            a.c1+a.c2+4.9 = b.c1 + 10
]]></query>

14.4.2 Outer Joins

An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.

Example 14-11 shows how to create a query q5 that uses an outer join between streams S0, with schema (c1 integer, c2 float), and S1, with schema (c1 integer, c2 float), using the (+) operator.

Example 14-11 Outer Joins

<query id="q5"><![CDATA[ 
    select a.c1+b.c1 
    from 
        S0[range 5] as a, 
        S1[range 3] as b 
    where 
        b.c2 = a.c2(+)
]]></query>

You can perform the following types of outer join:

For restrictions that you must observe when creating Oracle CQL outer joins, see Section 14.4.2.3, "Outer Join Restrictions".

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.

14.5 Oracle CQL Queries and the Oracle CEP Server Cache

You can access an Oracle CEP cache from an Oracle CQL statement or user-defined function.

For more information, see: