# 4 EPL Reference: Functions

This section contains information on the following subjects:

## 4.1 Single-row Functions

Single-row functions return a single value for every single result row generated by your statement. These functions can appear anywhere where expressions are allowed.

EPL allows static Java library methods as single-row functions, and also features built-in single-row functions.

EPL auto-imports the following Java library packages:

• `java.lang.*`

• `java.math.*`

• `java.text.*`

• `java.util.*`

Thus Java static library methods can be used in all expressions as shown in below example:

```  SELECT symbol, Math.round(volume/1000)
FROM StockTickEvent RETAIN 30 SECONDS
```

Other arbitrary Java classes may also be used, however their names must be fully qualified or configured to be imported. For more information, see Section 4.3, "User-Defined functions."

The table below outlines the built-in single-row functions available.

Table 4-1 Built-In Single-Row Functions

Single-row Function Result See
```MAX(expression, expression [, expression [,…])
```

Returns the highest numeric value among the two or more comma-separated expressions.

Section 4.1.1, "The MIN and MAX Functions"

```MIN(expression, expression [, expression [,…])
```

Returns the lowest numeric value among the two or more comma-separated expressions.

Section 4.1.1, "The MIN and MAX Functions"

```COALESCE(expression, expression [, expression [,…])
```

Returns the first non-null value in the list, or null if there are no non-null values.

Section 4.1.2, "The COALESCE Function"

```CASE value
WHEN compare_value THEN result
[WHEN compare_value THEN result …]
[ELSE result]
END
```

Returns result where the first value equals `compare_value`.

Section 4.1.3, "The CASE Control Flow Function"

```CASE value
WHEN condition THEN result
[WHEN condition THEN result …]
[ELSE result]
END
```

Returns the result for the first condition that is true.

Section 4.1.3, "The CASE Control Flow Function"

```PREV(expression, event_property)
```

Returns a property value of a previous event, relative to the event order within a data window.

Section 4.1.4, "The PREV Function"

```PRIOR(integer, event_property)
```

Returns a property value of a prior event, relative to the natural order of arrival of events

Section 4.1.5, "The PRIOR Function"

```SELECT
CASE
WHEN INSTANCEOF(item, com.mycompany.Service) THEN serviceName?
WHEN INSTANCEOF(item, com.mycompany.Product) THEN productName?
END
FROM OrderEvent
```

Returns a `boolean` value indicating whether the type of value returned by the expression is one of the given types

Section 4.1.6, "The INSTANCEOF Function"

```SELECT CAST(item.price?, double) FROM OrderEvent
```

Casts the return type of an expression to a designated type.

Section 4.1.7, "The CAST Function"

```SELECT EXISTS(item.serviceName?) FROM OrderEvent
```

Returns a boolean value indicating whether the dynamic property, provided as a parameter to the function, exists on the event.

Section 4.1.8, "The EXISTS Function"

You may also create user-defined single-row functions. See Section 4.3, "User-Defined functions".

### 4.1.1 The MIN and MAX Functions

The `MIN` and `MAX` functions take two or more expression parameters. The `MIN` function returns the lowest numeric value among these comma-separated expressions, while the `MAX` function returns the highest numeric value. The return type is the compatible aggregated type of all return values.

The next example shows the `MAX` function that has a `Double` return type and returns the value `1.1`.

```  SELECT MAX(1, 1.1, 2 * 0.5)
FROM ...
```

The `MIN` function returns the lowest value. The statement below uses the function to determine the smaller of two timestamp values.

```  SELECT symbol, MIN(ticks.timestamp, news.timestamp) AS minT
FROM StockTickEvent AS ticks, NewsEvent AS news RETAIN 30 SECONDS
WHERE ticks.symbol = news.symbol
```

The `MIN` and `MAX` functions are also available as aggregate functions. See Section 4.2, "Aggregate functions" for a description of this usage.

### 4.1.2 The COALESCE Function

The result of the `COALESCE` function is the first expression in a list of expressions that returns a non-null value. The return type is the compatible aggregated type of all return values.

This example returns a `String` type result with a value of `foo`.

```  SELECT COALESCE(NULL, 'foo')
FROM …
```

### 4.1.3 The CASE Control Flow Function

The `CASE` control flow function has two versions. The first version takes a value and a list of compare values to compare against, and returns the result where the first value equals the compare value. The second version takes a list of conditions and returns the result for the first condition that is true.

The return type of a `CASE` expression is the compatible aggregated type of all return values.

The example below shows the first version of a `CASE` statement. It has a `String` return type and returns the value `one`.

```  SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END
FROM …
```

The second version of the `CASE` function takes a list of conditions. The next example has a `Boolean` return type and returns the `Boolean` value `true`.

```  SELECT CASE WHEN 1>0 THEN true ELSE false END
FROM …
```

### 4.1.4 The PREV Function

The `PREV` function returns the property value of a previous event. The first parameter denotes the ith previous event in the order established by the data window. The second parameter is a property name for which the function returns the value for the previous event.

This example selects the value of the price property of the second previous event from the current `Trade` event.

```  SELECT PREV(2, price)
FROM Trade RETAIN 10 EVENTS
```

Because the `PREV` function takes the order established by the data window into account, the function works well with sorted windows. In the following example the statement selects the symbol of the three Trade events that had the largest, second-largest and third-largest volume.

```  SELECT PREV(0, symbol), PREV(1, symbol), PREV(2, symbol)
FROM Trade RETAIN 10 EVENTS WITH LARGEST volume
```

The ith previous event parameter can also be an expression returning an `Integer` type value. The next statement joins the `Trade` data window with a `RankSelectionEvent` event that provides a rank property used to look up a certain position in the sorted `Trade` data window:

```  SELECT PREV(rank, symbol)
FROM Trade, RankSelectionEvent RETAIN 10 EVENTS WITH LARGEST volume
```

The `PREV` function returns a NULL value if the data window does not currently hold the ith previous event. The example below illustrates this using a time batch window. Here the `PREV` function returns a null value for any events in which the previous event is not in the same batch of events. The `PRIOR` function as discussed below can be used if a null value is not the desired result.

```  SELECT PREV(1, symbol)
FROM Trade RETAIN BATCH OF 1 MINUTE
```

#### 4.1.4.1 Previous Event Per Group

The combination of the `PREV` function and the `PARTITION BY` clause returns the property value for a previous event in the given group.

For example, assume we want to obtain the price of the previous event of the same symbol as the current event.

The statement that follows solves this problem. It partitions the window on the symbol property over a time window of one minute. As a result, when the engine encounters a new symbol value that it hasn't seen before, it creates a new window specifically to hold events for that symbol. Consequently, the `PREV` function returns the previous event within the respective time window for that event's symbol value.

```  SELECT PREV(1, price) AS prevPrice
FROM Trade RETAIN 1 MIN PARTITION BY symbol
```

#### 4.1.4.2 Restrictions

The following restrictions apply to the `PREV` functions and its results:

• The function always returns a `null` value for remove stream (old data) events.

• The function may only be used on streams that are constrained by a `RETAIN` clause.

### 4.1.5 The PRIOR Function

The `PRIOR` function returns the property value of a prior event. The first parameter is an integer value that denotes the ith prior event in the natural order of arrival. The second parameter is a property name for which the function returns the value for the prior event.

This example selects the value of the price property of the second prior event to the current `Trade` event.

```  SELECT PRIOR(2, price)
FROM Trade RETAIN ALL
```

The `PRIOR` function can be used on any event stream or view and does not require a stream to be constrained by a `RETAIN` clause as with the `PREV` function. The function operates based on the order of arrival of events in the event stream that provides the events.

The next statement uses a length batch window to compute an average volume for every 3 `Trade` events, posting results every 3 events. The `SELECT` clause employs the `PRIOR` function to select the current average and the average before the current average:

```  SELECT AVG(volume) AS avgVolume, AVG(PRIOR(3, volume))
FROM Trade RETAIN BATCH OF 3 EVENTS
```

#### 4.1.5.1 Comparison to the PREV Function

The `PRIOR` function is similar to the `PREV` function. The key differences between the two functions are as follows:

• The `PREV` function returns previous events in the order provided by the window, while the `PRIOR` function returns prior events in the order of arrival in the stream.

• The `PREV` function requires a `RETAIN` clause while the `PRIOR` function does not.

• The `PREV` function returns the previous event taking into account any grouping. The `PRIOR` function returns prior events regardless of any grouping.

• The `PREV` function returns a null value for remove stream events, i.e. for events leaving a data window. The `PRIOR` function does not have this restriction.

### 4.1.6 The INSTANCEOF Function

The `INSTANCEOF` function returns a boolean value indicating whether the type of value returned by the expression is one of the given types. The first parameter to the `INSTANCEOF` function is an expression to evaluate. The second and subsequent parameters are Java type names.

The `INSTANCEOF` function determines the return type of the expression at runtime by evaluating the expression, and compares the type of object returned by the expression to the defined types. If the type of object returned by the expression matches any of the given types, the function returns true. If the expression returned null or a type that does not match any of the given types, the function returns false.

The `INSTANCEOF` function is often used in conjunction with dynamic (unchecked) properties. Dynamic properties are properties whose type is not known at compile type.

The following example uses the `INSTANCEOF` function to select different properties based on the type:

```  SELECT
CASE
WHEN INSTANCEOF(item, com.mycompany.Service) THEN serviceName?
WHEN INSTANCEOF(item, com.mycompany.Product) THEN productName?
END
FROM OrderEvent
```

The `INSTANCEOF` function returns false if the expression tested by `INSTANCEOF` returned null.

Valid parameters for the type parameter are:

• Any of the Java built-in types: `int`, `long`, `byte`, `short`, `char`, `double`, `float`, `string`, where `string` is a short notation for `java.lang.String`. The type name is not case-sensitive. For example, the following function tests if the dynamic `price` property is either of type `float` or type `double`:

```  INSTANCEOF(price?, double, float)
```
• The fully-qualified class name of the class to test, for example:

```  INSTANCEOF(product, org.myproducer.Product)
```

Valid parameters for the type parameter list are:

The `INSTANCEOF` function considers an event class's superclasses as well as all the directly or indirectly-implemented interfaces by superclasses.

### 4.1.7 The CAST Function

The `CAST` function casts the return type of an expression to a designated type. The function accepts two parameters: the first parameter is the property name or expression that returns the value to be casted and the second parameter is the type to cast to.

Valid parameters for the second (type) parameter are:

• Any of the Java built-in types: `int`, `long`, `byte`, `short`, `char`, `double`, `float`, `string`, where `string` is a short notation for `java.lang.String`. The type name is not case-sensitive. For example:

```  cast(price, double)
```
• The fully-qualified class name of the class to cast to, for example:

```  cast(product, org.myproducer.Product)
```

The `CAST` function is often used to provide a type for dynamic (unchecked) properties. Dynamic properties are properties whose type is not known at compile type. These properties are always of type `java.lang.Object`.

The following example shows how to use the `CAST` function to cast the `price` dynamic property of an `item` in the `OrderEvent` to a `double` value.

```  SELECT CAST(item.price?, double)
FROM OrderEvent
```

The `CAST` function returns a null value if the expression result cannot be casted to the desired type, or if the expression result itself is null.

The `CAST` function adheres to the following type conversion rules:

• For all numeric types, the `CAST` function utilizes `java.lang.Number` to convert numeric types, if required.

• For casts to `string` or `java.lang.String`, the `CAST` function calls `toString` on the expression result.

• For casts to other objects, including application objects, the `CAST` function considers a Java class's superclasses as well as all directly or indirectly-implemented interfaces by superclasses .

### 4.1.8 The EXISTS Function

The `EXISTS` function returns a boolean value indicating whether the dynamic property, provided as a parameter to the function, exists on the event. The `EXISTS` function accepts a single dynamic property name as its only parameter.

Use the `EXISTS` function with dynamic (unchecked) properties. Dynamic properties are properties whose type is not known at compile type. Dynamic properties return a null value if the dynamic property does not exist on an event, or if the dynamic property exists but the value of the dynamic property is null.

The following example of using the `EXISTS` function returns true if the `item` property contains an object that has a `serviceName` property. It returns false if the `item` property is null, or if the `item` property does not contain an object that has a property named `serviceName`:

```  SELECT EXISTS(item.serviceName?)
FROM OrderEvent
```

## 4.2 Aggregate functions

The aggregate functions are `SUM`, `AVG`, `COUNT`, `MAX`, `MIN`, `MEDIAN`, `STDDEV`, `AVEDEV`. You can use aggregate functions to calculate and summarize data from event properties. For example, to find out the total price for all stock tick events in the last 30 seconds:

```  SELECT SUM(price)
FROM StockTickEvent RETAIN 30 SECONDS
```

Here is the syntax for aggregate functions:

```  aggregate_function( [ALL | DISTINCT] expression)
```

You can apply aggregate functions to all events in an event stream window or other view, or to one or more groups of events. From each set of events to which an aggregate function is applied, EPL generates a single value.

The expression is usually an event property name. However it can also be a constant, function, or any combination of event property names, constants, and functions connected by arithmetic operators.

For example, to find out the average price for all stock tick events in the last 30 seconds if the price was doubled:

```  SELECT AVG(price * 2)
FROM StockTickEvent RETAIN 30 SECONDS
```

You can use the optional keyword `DISTINCT` with all aggregate functions to eliminate duplicate values before the aggregate function is applied. The optional keyword `ALL` which performs the operation on all events is the default.

The `MIN` and `MAX` aggregate functions are also available as single row functions. See Section 4.1.1, "The MIN and MAX Functions" for a description of this usage.

The syntax of the aggregation functions and the results they produce are shown in table below.

Table 4-2 Aggregate Functions

Aggregate Function Result
```SUM([ALL|DISTINCT] expression)
```

Totals the (distinct) values in the `expression`, returning a value of `long`, `double`, `float` or `integer` type depending on the expression.

```AVG([ALL|DISTINCT] expression)
```

Average of the (distinct) values in the `expression`, returning a value of `double` type.

```COUNT([ALL|DISTINCT] expression)
```

Number of the (distinct) non-null values in the `expression`, returning a value of `long` type.

```COUNT(*)
```

Number of events, returning a value of `long` type.

```MAX([ALL|DISTINCT] expression)
```

Highest (distinct) value in the `expression`, returning a value of the same type as the expression itself returns.

```MIN([ALL|DISTINCT] expression)
```

Lowest (distinct) value in the `expression`, returning a value of the same type as the expression itself returns.

```MEDIAN([ALL|DISTINCT] expression)
```

Median (distinct) value in the `expression`, returning a value of `double` type.

```STDDEV([ALL|DISTINCT] expression)
```

Standard deviation of the (distinct) values in the `expression`, returning a value of `double` type.

Oracle CEP uses a common measure called sample standard deviation when internally implementing STDDEV, rather than the precise mathematical definition. The definition used by Oracle CEP for STDDEV is as follows:

stddev(x) = (1/(N-1) * SUM{i=1 ... n}(xi - xavg) ) ^ (1/2)

```AVEDEV([ALL|DISTINCT] expression)
```

Mean deviation of the (distinct) values in the `expression`, returning a value of `double` type.

Oracle CEP uses the following definition for AVEDEV:

avedev(x) = 1/n * SUM{i=1 . . . n} (x_i ? xavg)

```TREND(expression)
```

Number of consecutive up ticks (as positive number), down ticks (as negative number), or no change (as zero) for `expression`.

You can use aggregation functions in a `SELECT` clause and in a `HAVING` clause. You cannot use aggregate functions in a `WHERE` clause, but you can use the `WHERE` clause to restrict the events to which the aggregate is applied. The next query computes the average and sum of the price of stock tick events for the symbol `ACME` only, for the last 10 stock tick events regardless of their symbol.

```  SELECT 'ACME stats' AS title, AVG(price) AS avgPrice, SUM(price) AS sumPrice
FROM StockTickEvent RETAIN 10 EVENTS
WHERE symbol='ACME'
```

In the preceding example the length window of 10 elements is not affected by the `WHERE` clause, in other words, all events enter and leave the length window regardless of their symbol. If we only care about the last 10 `ACME` events, we need to add a `MATCHING` clause as shown below.

```  SELECT 'ACME stats' AS title, AVG(price) AS avgPrice, SUM(price) AS sumPrice
FROM (SELECT * FROM StockTickEvent WHERE symbol='ACME')
RETAIN 10 EVENT
```

You can use aggregate functions with any type of event property or expression, with the following restriction:

• You can use `SUM`, `AVG`, `MEDIAN`, `STDDEV`, `AVEDEV` with numeric event properties only

EPL ignores any null values returned by the event property or expression on which the aggregate function is operating, except for the `COUNT(*)` function, which counts null values as well. All aggregate functions return null if the data set contains no events, or if all events in the data set contain only null values for the aggregated expression.

You may also create an aggregate user-defined function. For more information, see Section 4.3, "User-Defined functions".

## 4.3 User-Defined functions

A user-defined function can be invoked anywhere as an expression itself or within an expression. The function must simply be a public static method that the class loader can resolve at statement creation time. The engine resolves the function reference at statement creation time and verifies parameter types.

Caution:

Only idempotent user-defined functions in EPL queries are allowed. That is, multiple invocations of the user-defined function does not change the result.

The example below assumes a class `MyClass` that exposes a public static method `myFunction` accepting two parameters, and returning a numeric type such as `double`.

```  SELECT 3 * MyClass.myFunction(price, volume) as myValue
FROM StockTick RETAIN 30 SECONDS
```

User-defined functions also take array parameters as this example shows. Section 3.6, "Array Definition Operator" outlines in more detail the types of arrays produced.

```  SELECT *
FROM RFIDEvent RETAIN 10 MINUTES
WHERE com.mycompany.rfid.MyChecker.isInZone(zone, {10, 20, 30})
```

Oracle CEP supports both single-row and aggregate user-defined functions.

For more information, see "wlevs:function" in the Oracle Complex Event Processing Developer's Guide for Eclipse.