20 Oracle CQL Queries, Views, and Joins

This chapter provides reference and usage guidelines for queries, views, and joins in Oracle Continuous Query Language (Oracle CQL). 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 QUERY statement is called a query.

A top-level VIEW statement that you create using the 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.

This chapter includes the following sections:

For more information, see:

20.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 Event Processing CQL Processor to process data from one or more streams or views. For more information, see Section 20.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 20.3, "Views".

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

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

Example 20-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>
            <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 [Range Unbounded]
                WHERE  price > 10000
            ]]></query>
        </rules>
    </processor>
</n1:config>

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

  • view: contains Oracle CQL view statements (the Oracle CQL equivalent of subqueries). The view element id attribute defines the name of the view.

    In Example 20-1, the view element specifies an Oracle CQL view statement (the Oracle CQL equivalent of a subquery).

  • query: contains Oracle CQL select statements. The query element id attribute defines the name of the query.

    In Example 20-1, the query element specifies an Oracle CQL query statement. The query statement selects from the view. By default, the results of a query are output to a down-stream channel. You can control this behavior in the channel configuration using a selector element.

    For more information, see "Configuring a Channel" in the Oracle Fusion Middleware Developer's Guide for Oracle Event Processing for Eclipse.

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

For more information, see:

20.1.1 How to Create an Oracle CQL Query

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

To create an Oracle CQL query:

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

    For more information, see:

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

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

    Description of Figure 20-1 follows
    Description of "Figure 20-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 20-2 shows.

    Figure 20-2 Editing the Configuration Source for a Processor

    Description of Figure 20-2 follows
    Description of "Figure 20-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 Event Processing IDE for Eclipse, package your Oracle Event Processing application and deploy to the Oracle Event Processing server.

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

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

    For more information, see "Managing Oracle CQL Rules" in the Oracle Fusion Middleware Visualizer User's Guide for Oracle Event Processing.

20.2 Queries

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

query::=


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:

For more information, see:

20.2.1 Query Building Blocks

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

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

20.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 Event Processing 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 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.

For more information, see select_clause::=

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

If you create a join (see Section 20.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 20-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 20-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 Event Processing often does less work with fully qualified stream element names.

For more information, see:

20.2.1.4 Where Clause

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

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

20.2.1.6 Order By Clause

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

For more information, see:

20.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 Event Processing 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:

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

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

20.2.2 Simple Query

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

Example 20-3 Simple Query

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

For more information, see "Query".

20.2.3 Built-In Window Query

Example 20-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 20-4 Built-In Window Query

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

For more information, see:

20.2.4 MATCH_RECOGNIZE Query

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

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

20.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 more information, see, Section 20.6, "Oracle CQL Queries and Relational Database Tables"

20.2.6 XMLTABLE Query

Use this query to map the results of an XPath or XQuery expression into tuples.

XMLTABLE has the following sub-clauses:

  • XMLNAMESPACES -- Optional. A string with a set of XML namespace declarations that can be used in the query expression.

  • XQuery_string -- A string with the XQuery or XPath string to use to query the XML.

  • PASSING BY VALUE -- Points to the XML that is being used for input.

  • COLUMNS -- Optional. Defines the output properties of the result.

    • PATH -- Optional. A subclause of COLUMNS that specifies an XPath expression that points to where values for that property should be drawn from the XML.

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

For other examples, see "XMLTABLE Query Example" and "XMLTABLE With XML Namespaces Query Example".

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

20.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 20-7 shows a data cartridge TABLE clause that invokes the Oracle Spatial 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.

Example 20-7 Data Cartridge TABLE Query

<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 20-8 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 Event Processing invokes the data cartridge method only on the arrival of elements on the joined stream.

Example 20-8 Invalid Data Cartridge TABLE Query

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

For more examples, see:

For more information, see:

20.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 Event Processing cache event sources are pull data sources: that is, Oracle Event Processing polls the event source on arrival of an event on the data stream.

For more information, see Section 20.5, "Oracle CQL Queries and the Oracle Event Processing Server Cache".

20.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 Event Processing sorts values for the ORDER BY clause is specified by your Java locale.

20.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 20-9 shows.

Example 20-9 DIFFERENCE USING Clause

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

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

Table 20-1 DIFFERENCE USING Clause Affect on IStream

Input Relation Output

1000:    +5

{5}

+5

1000:    +6

{5, 6}

+6

1000:    +7

{5, 6, 7}

+7

1001:    +5

{5, 6, 7, 5}

 

1001:    +6

{5, 6, 7, 5, 6}

 

1001:    +7

{5, 6, 7, 5, 6, 7}

 

1001:    +8

{5, 6, 7, 5, 6, 7, 8}

+8

1002:    +5

{5, 6, 7, 5, 6, 7, 8, 5}

 

1003:    -5

{5, 6, 7, 5, 6, 7, 8}

 

1003:    -5

{5, 6, 7, 6, 7, 8}

 

1003:    -5

{6, 7, 6, 7, 8}

 

1003:    -6

{6, 7, 7, 8}

 

1003:    -6

{7, 7, 8}

 

1003:    -7

{7, 8}

 

1003:    -7

{8}

 

1003:    -8

{}

 

1004:    +5

{5}

+5


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 20-10 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 20-10 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 20-10 specifies the result of expression funct(c2, c3) by its position (3) in the DIFFERENCE USING clause usinglist.

Example 20-10 Specifying the usinglist in a DIFFERENCE USING Clause

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

20.2.11 Parameterized Queries

You can parameterize an Oracle CQL query and bind parameter values at run time using the :n character string as a placeholder, where n is a positive integer that corresponds to the position of the replacement value in a params element.

Note:

You cannot parameterize a view.

Example 20-11 shows a parameterized Oracle CQL query.

Example 20-11 Parameterized Oracle CQL Query

<n1:config xmlns:n1="http://www.bea.com/ns/wlevs/config/application" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    ...
    <processor>
        <name>myProcessor</name>
        <rules>
            <query id="MarketRule"><![CDATA[ 
                 SELECT symbol, AVG(price) AS average, :1 AS market
                 FROM StockTick [RANGE 5 SECONDS]
                 WHERE symbol = :2
            ]]></query>
        </rules>
        <bindings>
            <binding id="MarketRule">
                <params id="nasORCL">NASDAQ, ORCL</params>
                <params id="nyJPM">NYSE, JPM</params>
                <params id="nyWFC">NYSE, WFC</params>
            </binding>
        </bindings>
    </processor>
    <processor>
        <name>summarizeResults</name>
        <rules>
            <query id="SummarizeResultsRule"><![CDATA[ 
                select  
                    crossRate1 || crossRate2 as crossRatePair,
                    count(*) as totalCount,
                    :1 as averageInternalPrice
                from CrossRateStream 
                group by crossRate1,crossRate2 
                having :2 
            ]]></query>
        </rules>
        <bindings>
            <binding id="SummarizeResultsRule">
                <params id="avgcount">avg(internalPrice), count(*) > 0</params>
            </binding>           
        </bindings>
    </processor>
</n1:config>

In this example, the:

  • MarketRule query specifies two parameters: the third term in the SELECT and the value of symbol in the WHERE clause

  • SummarizeResultsRule query specifies two parameters: the third term in the SELECT and the value of the HAVING clause.

This section describes:

20.2.11.1 Parameterized Queries in Oracle CQL Statements

You may specify a placeholder anywhere an arithmetic expression 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:

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

20.2.11.3 Run-Time Query Naming

When a binding instantiates a parameterized query, Oracle Event Processing creates a new query at run time with the name queryId_paramId. For example, in Example 20-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.

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

Table 20-2 Parameterized Query Parameter Value Lexical Conventions

Convention Example Replacement Value

Primitive type literals

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

:1 = NASDAQ

:2 = 200.0

Oracle CQL fragments

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

:1 = count(*)

:2 = avg(val)

Quotes

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

:1 = 'alert'

:2 = "Seattle, WA"

:3 = 'fun' || "house"

:4 = one "two" 3


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

20.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 20-11 is effectively equivalent to the queries that Example 20-12 shows.

Example 20-12 Equivalent Queries at Runtime

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

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

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

A view is only accessible by the queries that reside in the same processor and cannot be exposed beyond that boundary.

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

For complete details on the view statement, see "View".

In Example 20-13, 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 20-13 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.

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

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

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

20.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 20-15 shows.

Example 20-15 Schema With Event Attribute Names Only

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

20.4 Joins

A join is a query that combines rows from two or more streams, views, or relations. Oracle Event Processing 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 20-16 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 20-16 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 Event Processing often does less work with fully qualified stream element names.

Oracle Event Processing supports the following types of joins:

Note:

When joining against a cache, you must observe additional query restrictions as Section 20.5.1, "Creating Joins Against the Cache" describes.

20.4.1 Inner Joins

By default, Oracle Event Processing 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 20-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).

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

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

Example 20-18 Outer Joins

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

20.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 20-19 shows. For all stream elements in A that have no matching stream elements in B, Oracle Event Processing returns null for any select list expressions containing stream elements of B.

Example 20-19 Left Outer Joins

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

20.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 20-20 shows. For all stream elements in B that have no matching stream elements in A, Oracle Event Processing returns null for any select list expressions containing stream elements of A.

Example 20-20 Right Outer Joins

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

20.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 20-21 shows.

Example 20-21 Outer Join Look-Back

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

20.5 Oracle CQL Queries and the Oracle Event Processing Server Cache

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

This section describes:

For more information, see:

20.5.1 Creating Joins Against the Cache

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

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

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

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

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

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

20.6 Oracle CQL Queries and Relational Database Tables

You can access a relational database table from an Oracle CQL query using:

  • table source: using a table source, you may join a stream only with a NOW window and only to a single database table.

    Note:

    Because changes in the table source are not coordinated in time with stream data, you may only join the table source to an event stream using a Now window and you may only join to a single database table. For more information, see "S[now]".

    To integrate arbitrarily complex SQL queries and multiple tables with your Oracle CQL queries, consider using the Oracle JDBC data cartridge instead.

    For more information, see "Configuring an Oracle CQL Processor Table Source" in the Oracle Fusion Middleware Developer's Guide for Oracle Event Processing for Eclipse.

  • Oracle JDBC data cartridge: using the Oracle JDBC data cartridge, you may integrate arbitrarily complex SQL queries and multiple tables and datasources with your Oracle CQL queries.

    For more information, see Section 17.1, "Understanding the Oracle Event Processing JDBC Data Cartridge".

    Note:

    Oracle recommends that you use the Oracle JDBC data cartridge to access relational database tables from an Oracle CQL statement.

In all cases, you must define datasources in the Oracle Event Processing server config.xml file. For more information, see "Configuring Access to a Relational Database" in the Oracle Fusion Middleware Administrator's Guide for Oracle Event Processing.

Oracle Event Processing relational database table event sources are pull data sources: that is, Oracle Event Processing polls the event source on arrival of an event on the data stream.

20.7 Oracle CQL Queries and Oracle Data Cartridges

You can access Oracle CQL data cartridge types in Oracle CQL queries just as you would Oracle CQL native types.

For more information, see: