17 Oracle CQL Queries, Views, and Joins

17.1.1 How to Create an Oracle CQL Query

Typically, you create an Oracle CQL query or view using the Oracle CEP IDE for Eclipse. After deployment, you can add, change, and delete Oracle CQL queries using the Oracle CEP Visualizer.

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 for Eclipse Projects" in the Oracle CEP Developer's Guide for Eclipse.

   • "Oracle CEP IDE for Eclipse and the Event Processing Network" in the Oracle CEP Developer's Guide for Eclipse.

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

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

   
   Description of "Figure 17-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 17-2 shows.

   Figure 17-2 Editing the Configuration Source for a Processor

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

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

   For examples, see

   • Section 17.2, "Queries"

   • Section 17.3, "Views"

   • Section 17.4, "Joins"

   • Section 17.7, "Oracle CQL Queries and Oracle Data Cartridges"

   • Section 17.5, "Oracle CQL Queries and the Oracle CEP Server Cache"

   • Chapter 4, "Operators"

   • Chapter 5, "Expressions"

   • Chapter 6, "Conditions"

   • Chapter 8, "Built-In Single-Row Functions"

   • Chapter 9, "Built-In Aggregate Functions"

   • Chapter 10, "Colt Single-Row Functions"

   • Chapter 11, "Colt Aggregate Functions"

   • Chapter 12, "java.lang.Math Functions"

   • Chapter 13, "User-Defined Functions"

   • Chapter 18, "Pattern Recognition With MATCH_RECOGNIZE"

   • Chapter 19, "Oracle CQL Statements"

   • Oracle CEP Getting Started

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

   For more information, see "Assembling and Deploying Oracle CEP Applications" in the Oracle CEP Developer's Guide for Eclipse.

5. After deployment, use the Oracle CEP Visualizer to change, add, and delete queries in the Oracle CEP application.

   For more information, see "Managing Oracle CQL Rules" in the Oracle CEP Visualizer User's Guide.

17.2.1 Query Building Blocks

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

• Section 17.2.1.1, "Select, From, Where Block"

• Section 17.2.1.2, "Select Clause"

• Section 17.2.1.3, "From Clause"

• Section 17.2.1.4, "Where Clause"

• Section 17.2.1.5, "Group By Clause"

• Section 17.2.1.6, "Order By Clause"

• Section 17.2.1.7, "Having Clause"

• Section 17.2.1.8, "Binary Clause"

• Section 17.2.1.9, "IDStream Clause"

17.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 17.2.1.2, "Select Clause"

• Section 17.2.1.3, "From Clause"

• Section 17.2.1.4, "Where Clause"

• Section 17.2.1.5, "Group By Clause"

• Section 17.2.1.6, "Order By Clause"

• Section 17.2.1.7, "Having Clause"

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

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

For more information, see:

• Section 17.2.4, "MATCH_RECOGNIZE Query"

• Section 17.2.6, "XMLTable Query"

• Section 17.2.7, "Function TABLE Query"

• relation_variable::=

17.2.2 Simple Query

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

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

For more information, see "Query".

17.2.3 Built-In Window Query

Example 17-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.

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

For more information, see:

• Section 1.1.3, "Stream-to-Relation Operators (Windows)"

• window_type::=

17.2.4 MATCH_RECOGNIZE Query

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

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

• Chapter 18, "Pattern Recognition With MATCH_RECOGNIZE"

• pattern_recognition_clause::=

17.2.5 Relational Database Table Query

Using an Oracle CQL processor, you can specify a relational database table as an event source. You can query this event source, join it with other event sources, and so on.

For example, assume that you create the table you want to access using the SQL statement that Example 17-6 shows.

create table Stock (symbol varchar(16), exchange varchar(16));

After configuration, you can define Oracle CQL queries that access the Stock table as if it was just another event stream. Example 17-7 shows a query that joins one event stream ExchangeStream with the Stock table:

SELECT ExchangeStream.symbol, ExchangeStream.price, Stock.exchange
FROM   ExchangeStream [Now], Stock
WHERE  ExchangeStream.symbol = Stock.symbol

Oracle CEP relational database table event sources are pull data sources: that is, Oracle CEP will periodically poll the event source.

For more information, see:

• "Configuring Access to a Relational Database" in the Oracle CEP Administrator's Guide

• "Configuring an Oracle CQL Processor Table Source" in the Oracle CEP Developer's Guide for Eclipse

17.2.6 XMLTable Query

Example 17-8 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. The XMLTABLE clause also supports XML namespaces.

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

• Section 1.1.5, "Stream-to-Stream Operators"

• xmltable_clause::=

• "SQL/XML (SQLX)"

17.2.7 Function TABLE Query

Use the TABLE clause to access the multiple rows returned by a built-in or user-defined function in the FROM clause of an Oracle CQL query. The TABLE clause converts the set of returned rows into an Oracle CQL relation. Because this is an external relation, you must join the TABLE function clause with a stream.

table_clause::=

(object_expr::=, identifier::=, datatype::=)

Note the following:

• The function must return an array type or Collection type.

• You must join the TABLE function clause with a stream.

Example 17-9 shows a data cartridge TABLE clause that invokes the Oracle Spatial data cartridge method getContainingGeometries, passing in one parameter (InputPoints.point). The return value of this method, a Collection, is aliased as validGeometries. The relation that the TABLE clause returns is aliased as R2.

<query id="q1"><![CDATA[ 
RSTREAM (
    SELECT 
        R2.validGeometries.shape as containingGeometry,
        R1.point as inputPoint
    FROM 
        InputPoints[now] as R1,
        TABLE (getContainingGeometries@spatial (InputPoints.point) as validGeometries) AS R2
)
]]></query>

Example 17-10 shows an invalid data cartridge TABLE query that fails to join the data cartridge TABLE clause with another stream because the function getAllGeometries@spatial was called without any parameters. Oracle CEP invokes the data cartridge method only on the arrival of elements on the joined stream.

<query id="q2"><![CDATA[ 
RSTREAM (
    SELECT 
        R2.validGeometries.shape as containingGeometry
    FROM 
        TABLE (getAllGeometries@spatial () as validGeometries) AS R2
)
]]></query>

For more examples, see:

• "Data Cartridge TABLE Query Example: Iterator"

• "Data Cartridge TABLE Query Example: Array"

• "Data Cartridge TABLE Query Example: Collection"

For more information, see:

• Section 17.7, "Oracle CQL Queries and Oracle Data Cartridges"

• Section 1.1.11, "Functions"

• Section 15.1.4.3, "Arrays"

• Section 15.1.4.4, "Collections"

17.2.8 Cache Query

Using an Oracle CQL processor, you can specify a cache as an event source. You can query this event source and join it with other event sources using a Now window only.

Oracle CEP cache event sources are pull data sources: that is, Oracle CEP will periodically poll the event source.

For more information, see Section 17.5, "Oracle CQL Queries and the Oracle CEP Server Cache".

17.2.9 Sorting Query Results

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

order_by_clause::=

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

17.2.10 Detecting Differences in Query Results

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

using_clause::=

(usinglist::=)

Consider the query that Example 17-11 shows.

<query id="q0">
    ISTREAM (
        SELECT c1 FROM S [RANGE 1 NANOSECONDS]
    )    DIFFERENCE USING (c1)
</query>

Table 17-1 shows sample input for this query. The Relation column shows the contents of the relation S [RANGE 1 NANOSECONDS] and the Output column shows the query results after the DIFFERENCE USING clause is applied. This clause allows you to succinctly detect only differences in the IStream output.

When you specify the usinglist in the DIFFERENCE USING clause, you may specify columns by:

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

  Example 17-12 shows attribute name c1 in the DIFFERENCE USING clause usinglist.

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

  Example 17-12 shows alias logval in the DIFFERENCE USING clause usinglist.

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

  Example 17-12 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>

You can use the DIFFERENCE USING clause with both IStream and DStream operators.

For more information, see:

• using_clause::=

• "IStream Relation-to-Stream Operator"

• "DStream Relation-to-Stream Operator"

17.3.1 Views and Joins

If you create a join 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 17-14 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.

<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 a best practice to always qualify stream element references explicitly. Oracle CEP often does less work with fully qualified stream element names.

For more information, see Section 17.4, "Joins".

17.3.2 Views and Schemas

You may define the optional schema of the view using a space delimited list of event attribute names as Example 17-15 shows.

<view id="MAXBIDMINASK" schema="cusip bidseq"><![CDATA[ 
    select ... 
]]></view>

17.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 17-17 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).

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

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

You specify an outer join in the FROM clause of a query using LEFT or RIGHT OUTER JOIN ... ON syntax.

from_clause::=

(non_mt_relation_list::=, relation_variable::=, non_mt_cond_list::=)

Example 17-18 shows how to create a query q5 that uses a left outer join between streams S0, with schema (c1 integer, c2 float), and S1, with schema (c1 integer, c2 float).

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

Use the ON clause to specify a join condition. Doing so lets you specify join conditions separate from any search or filter conditions in the WHERE clause.

You can perform the following types of outer join:

• Section 17.4.2.1, "Left Outer Join"

• Section 17.4.2.2, "Right Outer Join"

• Section 17.4.2.3, "Outer Join Look-Back"

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

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

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

17.5.1 Creating Joins Against the Cache

When writing Oracle CQL queries that join against a cache, you must observe the following restrictions:

• Section 17.5.1.1, "Cache Key First and Simple Equality"

• Section 17.5.1.2, "No Arithmetic Operations on Cache Keys"

• Section 17.5.1.3, "No Full Scans"

• Section 17.5.1.4, "Multiple Conditions and Inequality"

For more information, see Section 17.4, "Joins".

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

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

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

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

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

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

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

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

... CACHE.C1 > 5 ...

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

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

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