5 Expressions

A reference to expressions in Oracle Continuous Query Language (Oracle CQL) is provided. An expression is a combination of one or more values and one or more operations, including a constant having a definite value, a function that evaluates to a value, or an attribute containing a value.

Every expression maps to a data type. This simple expression evaluates to 4 and has data type NUMBER (the same data type as its components):

2*2 

The following expression is an example of a more complex expression that uses both functions and operators. The expression adds seven days to the current date, removes the time component from the sum, and converts the result to CHAR data type:

TO_CHAR(TRUNC(SYSDATE+7))

5.1 Introduction to Expressions

GGSA supports the following expressions:

• Aggregate distinct expressions: aggr_distinct_expr.

• Aggregate expressions: aggr_expr.

• Arithmetic expressions: arith_expr.

• Arithmetic expression list: arith_expr_list

• Case expressions: case_expr.

• Decode expressions: decode.

• Function expressions: func_expr.

• Order expressions: order_expr.

You can use expressions in:

• The select list of the SELECT statement

• A condition of the WHERE clause and HAVING clause

GGSA does not accept all forms of expressions in all parts of all Oracle CQL statements. Refer to the individual Oracle CQL statements in Oracle CQL Statements for information on restrictions on the expressions in that statement.

You must use appropriate expression notation whenever expr appears in conditions, Oracle CQL functions, or Oracle CQL statements in other parts of this reference. The sections that follow describe and provide examples of the various forms of expressions.

Note:

In stream input examples, lines beginning with h (such as h 3800) are heartbeat input tuples. These inform GGSA that no further input will have a timestamp lesser than the heartbeat value.

5.2 aggr_distinct_expr

Use an aggr_distinct_expr aggregate expression when you want to use an aggregate built-in function with distinct. When you want to use an aggregate built-in function without distinct, see aggr_expr.

aggr_distinct_expr::=

(arith_expr::=)

You can specify an arith_distinct_expr as the argument of an aggregate expression.

You can use an aggr_distinct_expr in the following Oracle CQL statements:

• arith_expr::=

For more information, see Built-In Aggregate Functions.

5.3 aggr_expr

Use an aggr_expr aggregate expression when you want to use aggregate built-in functions. When you want to use an aggregate built-in function with distinct, see aggr_distinct_expr.

aggr_expr::=

(arith_expr::= )

You can specify an arith_expr as the argument of an aggregate expression.

The count aggregate built-in function takes a single argument made up of any of the values that Table 5-1 lists and returns the int value indicated.

Table 5-1 Return Values for COUNT Aggregate Function

Input Argument	Return Value
arith_expr	The number of tuples where arith_expr is not null.
*	The number of tuples matching all the correlation variables in the pattern, including duplicates and nulls.
identifier.*	The number of all tuples that match the correlation variable identifier, including duplicates and nulls.
identifier.attr	The number of tuples that match correlation variable identifier, where attr is not null.

The first and last aggregate built-in functions take a single argument made up of the following period separated values:

• identifier1: the name of a pattern as specified in a DEFINE clause.

• identifier2: the name of a stream element as specified in a CREATE STREAM statement.

You can use an aggr_expr in the following Oracle CQL statements:

• arith_expr::=

For more information, see:

• Built-In Aggregate Functions

• Using count With *, identifier.*, and identifier.attr

• first

• last.

5.4 arith_expr

Use an arith_expr arithmetic expression to define an arithmetic expression using any combination of stream element attribute values, constant values, the results of a function expression, aggregate built-in function, case expression, or decode. You can use all of the normal arithmetic operators (+,-,*, and /) and the concatenate operator (||).

arith_expr::=

(func_expr::=, aggr_expr::=, aggr_distinct_expr::=, case_expr::=, decode::=, arith_expr::=)

You can use an arith_expr in the following Oracle CQL statements:

• aggr_distinct_expr::=

• aggr_expr::=

• arith_expr::=

• case_expr::=

• searched_case::=

• simple_case::=

• condition::=

• between_condition::=

• param_list

• measure_column::=.

For more information, see Arithmetic Operators.

5.5 arith_expr_list

Use an arith_expr_list arithmetic expression list to define one or more arithmetic expressions using any combination of stream element attribute values, constant values, the results of a function expression, aggregate built-in function, case expression, or decode. You can use all of the normal arithmetic operators (+,-,*, and /) and the concatenate operator (||).

arith_expr_list::=

(arith_expr::=)

For more information, see Arithmetic Operators.

5.5.1 Examples

The following example shows how to use a arith_expr_list expression.

<query id="q1"><![CDATA[ 
    select
        XMLELEMENT("Emp", XMLELEMENT("Name", e.job_id||' '||e.last_name),XMLELEMENT("Hiredate", e.hire_date)
        )
    from 
        tkdata51_S0 [range 1] as e
]]></query>

5.6 case_expr

Use a case_expr case expression to evaluate stream elements against multiple conditions.

case_expr::=

(searched_case_list::=, arith_expr::=, simple_case_list::=)

searched_case_list::=

(searched_case::=)

searched_case::=

(arith_expr::=)

simple_case_list::=

(simple_case::=)

simple_case::=

(arith_expr::=)

The case_expr is similar to the DECODE clause of an arithmetic expression (see decode).

In a searched_case clause, when the non_mt_cond_list evaluates to true, the searched_case clause may return either an arithmetic expression or null.

In a simple_case clause, when the arithmetic expression is true, the simple_case clause may return either another arithmetic expression or null.

You can use a case_expr in the following Oracle CQL statements:

• arith_expr::=.

5.6.1 Examples

This section describes the following case_expr examples:

• case_expr with SELECT *

• case_expr with SELECT.

case_expr with SELECT *

Consider the query q97 and the data stream S0. Stream S1 has schema (c1 integer, c2 float). The query returns the relation.

<query id="q97"><![CDATA[ 
    select * from S0 
    where 
        case 
            when c2 < 25 then c2+5 
            when c2 > 25 then c2+10 
        end > 25
]]></query>

Timestamp   Tuple
     1000    0.1,10
     1002   0.14,15
   200000    0.2,20
   400000    0.3,30
   500000    0.3,35
   600000       ,35
h 800000
100000000   4.04,40
h 200000000

Timestamp   Tuple Kind  Tuple
400000:+ 0.3,30
500000:+ 0.3,35
600000:+ ,35
100000000:+ 4.04,40

case_expr with SELECT

Consider the query q96 and the data streams S0 and S1. Stream S0 has schema (c1 float, c2 integer) and stream S1 has schema (c1 float, c2 integer). The query returns the relation.

<query id="q96"><![CDATA[ 
    select 
        case to_float(S0.c2+10) 
            when (S1.c2*100)+10 then S0.c1+0.5 
            when (S1.c2*100)+11 then S0.c1 
            else S0.c1+0.3 
        end 
    from 
        S0[rows 100], 
        S1[rows 100]
]]></query>

Timestamp   Tuple
     1000    0.1,10
     1002   0.14,15
   200000    0.2,20
   400000    0.3,30
   500000    0.3,35
   600000       ,35
h 800000
100000000   4.04,40
h 200000000

Timestamp   Tuple
     1000   10,0.1
     1002   15,0.14
   200000   20,0.2
   300000     ,0.2
   400000   30,0.3
100000000   40,4.04

Timestamp   Tuple Kind  Tuple
     1000:  +           0.6
     1002:  +           0.44
     1002:  +           0.4
     1002:  +           0.14
   200000:  +           0.5
   200000:  +           0.5
   200000:  +           0.4
   200000:  +           0.44
   200000:  +           0.7
   300000:  +           0.4
   300000:  +           0.44
   300000:  +           0.7
   400000:  +           0.6
   400000:  +           0.6
   400000:  +           0.6
   400000:  +           0.6
   400000:  +           0.4
   400000:  +           0.44
   400000:  +           0.5
   400000:  +           0.8
   500000:  +           0.6
   500000:  +           0.6
   500000:  +           0.6
   500000:  +           0.6
   500000:  +           0.6
   600000:  +
   600000:  +
   600000:  +
   600000:  +
   600000:  +
100000000:  +           4.34
100000000:  +           4.34
100000000:  +           4.34
100000000:  +           4.34
100000000:  +           4.34
100000000:  +           0.4
100000000:  +           0.44
100000000:  +           0.5
100000000:  +           0.6
100000000:  +           0.6
100000000:  +
100000000:  +           4.34

5.7 decode

Use a decode expression to evaluate stream elements against multiple conditions.

decode::=

expr, search1, result1, search2, result2, ... , searchN, result N, default

DECODE compares expr to each search value one by one. If expr equals a search value, the DECODE expressions returns the corresponding result. If no match is found, the DECODE expressions returns default. If default is omitted, the DECODE expressions returns null.

The arguments can be any of the numeric (INTEGER, BIGINT, FLOAT, or DOUBLE) or character (CHAR) data types. For more information, see Data Types).

The search, result, and default values can be derived from expressions. GGSA uses short-circuit evaluation. It evaluates each search value only before comparing it to expr, rather than evaluating all search values before comparing any of them with expr. Consequently, GGSA never evaluates a search i, if a previous search j (0 < j < i) equals expr.

GGSA automatically converts expr and each search value to the data type of the first search value before comparing. GGSA automatically converts the return value to the same data type as the first result.

In a DECODE expression, GGSA considers two nulls to be equivalent. If expr is null, then GGSA returns the result of the first search that is also null.

The maximum number of components in the DECODE expression, including expr, searches, results, and default, is 255.

The decode expression is similar to the case_expr (see case_expr::=).

You can use a decode expression in the following Oracle CQL statements:

• arith_expr::=.

5.7.1 Examples

Consider the query q and the input relation R. Relation R has schema (c1 float, c2 integer). The query returns the relation.

<query id="q"><![CDATA[ 
...
    SELECT DECODE (c2, 10, c1+0.5, 20, c1+0.1, 30, c1+0.2, c1+0.3) from R
]]></query>

Timestamp   Tuple Kind  Tuple
     1000:    +    0.1,10
     1002:    +    0.14,15
     2000:    -    0.1,10
     2002:    -    0.14,15
   200000:    +    0.2,20
   201000:    -    0.2,20
   400000:    +    0.3,30
   401000:    -    0.3,30
   500000:    +    0.3,35
   501000:    -    0.3,35
   600000:    +    0.3,35
   601000:    -    0.3,35
100000000:    +    4.04,40
100001000:    -    4.04,40

Timestamp   Tuple Kind  Tuple
   1000:    +    0.6
   1002:    +    0.44
   2000:    -    0.1,10
   2002:    -    0.14,15
 200000:    +    0.3
 201000:    -    0.2,20
 400000:    +    0.5
 401000:    -    0.3,30
 500000:    +    0.6
 501000:    -    0.3,35
100000000:  +    4.34
100001000:  -    4.34

5.8 func_expr

Use the func_expr function expression to define a function invocation using any Oracle CQL built-in, user-defined, or Oracle data cartridge function.

func_expr::=

arith_expr::=)

:=

func_name

You can specify the identifier of a function explicitly:

• with an empty argument list.

• with an argument list of one or more arguments.

• with a distinct arithmetic expression.

  For more information, see aggr_distinct_expr.

PREV

The PREV function takes a single argument made up of the following period-separated identifier arguments:

• identifier1: the name of a pattern as specified in a DEFINE clause.

• identifier2: the name of a stream element as specified in a CREATE STREAM statement.

The PREV function also takes the following const_int arguments:

• const_int: the index of the stream element before the current stream element to compare against. Default: 1.

• const_bigint: the timestamp of the stream element before the current stream element to compare against. To obtain the timestamp of a stream element, you can use the ELEMENT_TIME pseudocolumn (see ELEMENT_TIME Pseudocolumn).

For more information, see prev. For an example, see func_expr PREV Function Example.

FIRST and LAST

The FIRST and LAST functions each take a single argument made up of the following period-separated values:

• identifier1: the name of a pattern as specified in a DEFINE clause.

• identifier2: the name of a stream element as specified in a CREATE STREAM statement.

For more information, see:

• first

• last

You can specify the identifier of a function explicitly with or without a non_mt_arg_list: a list of arguments appropriate for the built-in or user-defined function being invoked. The list can have single or multiple arguments.

You can use a func_expr in the following Oracle CQL statements:

• arith_expr::=

For more information, see Functions.

5.8.1 Examples

This section describes the following func_expr examples:

• func_expr PREV Function Example

func_expr PREV Function Example

The following example shows how to compose a func_expr to invoke the PREV function.

<query id="q36"><![CDATA[ 
    select T.Ac1 from S15 
    MATCH_RECOGNIZE ( 
        PARTITION BY
            c2 
        MEASURES 
            A.c1 as Ac1 
        PATTERN(A) 
        DEFINE 
            A as (A.c1 = PREV(A.c1,3,5000) )
    ) as T
]]></query>

The following example shows how to compose a func_expr to invoke the SUM function.

<query id="q3"><![CDATA[ 
    select sum(c2) from S1[range 5]
]]></query>

5.9 order_expr

Use the order_expr expression to specify the sort order in which GGSA returns tuples that a query selects.

order_expr::=

You can specify a stream element by attr name.

Alternatively, you can specify a stream element by its const_int index where the index corresponds to the stream element position you specify at the time you register or create the stream.

5.9.1 Examples

Stream S3 has schema (c1 bigint, c2 interval, c3 byte(10), c4 float). This example shows how to order the results of query q210 by c1 and then c2 and how to order the results of query q211 by c2, then by the stream element at index 3 (c3) and then by the stream element at index 4 (c4).

<query id="q210"><![CDATA[ 
    select * from S3 order by c1 desc nulls first, c2 desc nulls last
]]></query>
<query id="q211"><![CDATA[ 
    select * from S3 order by c2 desc nulls first, 3 desc nulls last, 4 desc
]]></query>