Skip Headers
Oracle® Complex Event Processing CQL Language Reference
11g Release 1 (11.1.1)

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

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

3 Pseudocolumns

A pseudocolumn behaves like a stream element, but is not actually part of the tuple.

3.1 Introduction to Pseudocolumns

You can select from pseudocolumns, but you cannot modify their values. A pseudocolumn is also similar to a function without arguments (see Section 1.1.11, "Functions").

Oracle CQL supports the following pseudocolumns:

3.2 ELEMENT_TIME Pseudocolumn

The ELEMENT_TIME pseudocolumn returns the timestamp value associated with a given stream element as a java.lang.Math.Bigint(Long).

For syntax, see pseudo_column::=.

This section describes how to use ELEMENT_TIME in various queries, including:

3.2.1 Using ELEMENT_TIME With SELECT

Example 3-1 shows how you can use the ELEMENT_TIME pseudocolumn in a select statement. Stream S1 has schema (c1 integer). Given the input stream that Example 3-2 shows, this query returns the results that Example 3-3 shows. Note that the function to_timestamp is used to convert the Long values to timestamp values.

Example 3-1 ELEMENT_TIME Pseudocolumn in a Select Statement

<query id="q4"><![CDATA[ 
    select 
        c1, 
        to_timestamp(element_time) 
    from 
        S1[range 10000000 nanoseconds slide 10000000 nanoseconds]
]]></query>

Example 3-2 Input Stream

Timestamp   Tuple
 8000       80
 9000       90
13000       130
15000       150
23000       230
25000       250

Example 3-3 Output Relation

Timestamp   Tuple Kind  Tuple
 8000       +           80,12/31/1969 17:00:08
 8010       -           80,12/31/1969 17:00:08
 9000       +           90,12/31/1969 17:00:09
 9010       -           90,12/31/1969 17:00:09
13000       +           130,12/31/1969 17:00:13
13010       -           130,12/31/1969 17:00:13
15000       +           150,12/31/1969 17:00:15
15010       -           150,12/31/1969 17:00:15
23000       +           230,12/31/1969 17:00:23
23010       -           230,12/31/1969 17:00:23
25000       +           250,12/31/1969 17:00:25
25010       -           250,12/31/1969 17:00:25

If your query includes a GROUP BY clause, you cannot use the ELEMENT_TIME pseudocolumn in the SELECT statement directly. Instead, use a view as Section 3.2.2, "Using ELEMENT_TIME With GROUP BY" describes.

3.2.2 Using ELEMENT_TIME With GROUP BY

Consider query Q1 that Example 3-4 shows. You cannot use ELEMENT_TIME in the SELECT statement of the query because of the GROUP BY clause.

Example 3-4 Query With GROUP BY

<query id="Q1"><![CDATA[ 
    SELECT
        R.queryText AS queryText,
        COUNT(*) AS queryCount
    FROM
        queryEventChannel [range 30 seconds] AS R
    GROUP BY
        queryText
]]></query>

Instead, create a view as Example 3-5 shows. The derived stream corresponding to V1 will contain a stream element each time (queryText, queryCount, maxTime) changes for a specific queryText group.

Example 3-5 View

<view id="V1"><![CDATA[ 
    ISTREAM (
        SELECT
            R.queryText AS queryText,
            COUNT(*) AS queryCount,
            MAX(R.ELEMENT_TIME) as maxTime
        FROM
            queryEventChannel [range 30 seconds] AS R
        GROUP BY
            queryText
    )
]]></view>

To select these query elements, create a query as Example 3-6 shows.

Example 3-6 Query

<query id="Q1"><![CDATA[ 
    SELECT
        queryText,
        queryCount,
        ELEMENT_TIME as eventTime
    FROM
        V1
]]></query>

3.2.3 Using ELEMENT_TIME With PATTERN

Example 3-7 shows how the ELEMENT_TIME pseudocolumn can be used in a pattern query. Here a tuple or event matches correlation variable Nth if the value of Nth.status is >= F.status and if the difference between the Nth.ELEMENT_TIME value of that tuple and the tuple that last matched F is less than the given interval as a java.lang.Math.Bigint(Long).

Example 3-7 ELEMENT_TIME Pseudocolumn in a Pattern

...
PATTERN (F Nth+? L)
        DEFINE
            Nth AS 
                Nth.status >= F.status
                AND 
                Nth.ELEMENT_TIME - F.ELEMENT_TIME < 10000000000L,
            L AS 
                L.status >= F.status 
                AND 
                count(Nth.*) = 3
                AND L.ELEMENT_TIME - F.ELEMENT_TIME < 10000000000L 
...