The following example 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.

The following example 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.

tkdata2.cqlx

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

For more information, see:

• Stream-to-Relation Operators (Windows).

The following example shows a query that selects all stream elements from stream S12, with schema (c1 integer, c2 float), using a user-defined window of type range_slide based on user-defined Java class MyRangeSlideWindow.java.

<rule id="range_slide"><![CDATA[ 
    register window range_slide(winrange int, winslide int) implement using "MyRangeSlideWindow"
]]></rule>
<query id="q79"><![CDATA[ 
    select * from S12 [range_slide(10,5)]
]]></query>

For more information, see User-Defined Stream-to-Relation Window Operators.

The following example 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:

• Pattern Recognition With MATCH_RECOGNIZE.

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.

The following example 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 data types 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:

• Stream-to-Stream Operators

• SQL/XML (SQLX).

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.

The following example 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.

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

The following example 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.

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

For more examples, see Functions.

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

order_by_clause::=

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.

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

using_clause::=

Consider the query.

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

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

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

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

  The following example specifies the result of expression funct(c2, c3) by its position (3) in the DIFFERENCE USING clause usinglist.

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

For more information, see:

• IStream Relation-to-Stream Operator

• DStream Relation-to-Stream Operator.

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.

The following example shows a 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:

• Parameterized Queries in Oracle CQL Statements

• The bindings Element

• Run-Time Query Naming

• Lexical Conventions for Parameter Values

• Parameterized Queries at Runtime

• Replacing Parameters Programmatically.

A subquery can be specified in the FROM clauses of a queries where sources relations/streams are specified. Subquery support will also be extended to SET queries.

CREATE QUERY q0 AS
SELECT  prodid, sum(sales)
FROM (SELECT prodid AS prodid, sales AS sales FROM sales_stream [RANGE 24 HOURS]) AS foo
GROUP BY prodid;