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 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. The second parameter is the type to cast to. You can use the as keyword instead of comma (,) to separate parameters.

Valid parameters for the second (type) parameter are:

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 cast function as shown in the next statement casts the dynamic "price" 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:

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-typed result of value 'foo':

select coalesce(null, 'foo') from ...

The current_evaluation_context function takes no parameters and returns expression evaluation contextual information as an object of type com.espertech.esper.client.hook.EPLExpressionEvaluationContext. The object provides the engine URI, the statement name and the context partition id of the currently-evaluated expression.

For example:

select current_evaluation_context().getEngineURI() as engineURI from MyEvent

The context partition id will be -1 when the statement is not associated to a context.

The current_timestamp function is a reserved keyword and requires no parameters. The result of the current_timestamp function is the long-type millisecond value of the current engine system time.

The function returns the current engine timestamp at the time of expression evaluation. When using external-timer events, the function provides the last value of the externally-supplied time at the time of expression evaluation.

This example selects the current engine time:

select current_timestamp from MyEvent
// equivalent to
select current_timestamp() from MyEvent

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.

The exists function is for use 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 exists on an event, or if the dynamic property exists but the value of the dynamic property is null.

The exists function as shown next 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

The grouping function is a SQL-standard function useful in statements that have a group by-clause and that utilize one of the rollup, cube or grouping sets keywords. The function can be used only in the select-clause, having-clause and order by-clauses.

The function takes a single expression as a parameter and returns an integer value of zero or one indicating whether a specified expression in a group-by-clause is aggregated or not. The function returns 1 for aggregated or 0 for not aggregated.

The grouping function can help you distinguish null values returned because of the output row's aggregation level from null values returned by event properties or other expressions.

The parameter expression must match exactly one of the expressions in the group-by-clause.

Please see an example in the next section.

The grouping_id function is a SQL-standard function useful in statements that have a group by-clause and that utilize one of the rollup, cube or grouping sets keywords. The function can be used only in the select-clause, having-clause and order by-clauses.

The function takes one or more expressions as a parameter and returns an integer value indicating grouping level. The engine computes the grouping level by taking the results of multiple grouping functions and concatenating them into a bit vector (a string of ones and zeros).

Assume a car event that has a property for name, place and number of cars:

create schema CarEvent(name string, place string, numcars int)

The next EPL computes the total number of cars for each of the following groupings: per name and place, per name, per place and overall.

select name, place, sum(numcars), grouping(name), grouping(place), grouping_id(name, place)
from CarEvent group by grouping sets((name, place),name, place,())

Assume your application processes a car event with properties like so: CarEvent={name='skoda', place='france', numcars=100}.

The engine outputs 4 rows as shown in the next table:

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

This 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 list are:

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

The istream function returns a boolean value indicating whether within the context of expression evaluation the current event or set of events (joins) are part of the insert stream (true) or part of the remove stream (false). The function takes no parameters.

Use the istream function with data windows and select irstream and insert irstream into.

In the following example the istream function always returns boolean true since no data window is declared:

select irstream *, istream() from OrderEvent

The next example declares a data window. For newly arriving events the function returns boolean true, for events that expire after 10 seconds the function returns boolean false:

select irstream *, istream() from OrderEvent.win:time(10 sec)

The istream function returns true for all cases where insert or remove stream does not apply, such as when used in parameter expressions to data windows or in stream filter expressions.

The min and max function take two or more parameters that itself can be expressions. The min function returns the lowest numeric value among the 2 or more 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.win:time(30 sec) as ticks, NewsEvent.win:time(30 sec) as news
	where ticks.symbol = news.symbol

The prev function returns the property value or all event properties of a previous event. For data windows that introduce a sort order other than the order of arrival, such as the sorted data window and the time order data window, the function returns the event at the specified position.

The prevtail function returns the property value or all event properties of the positional-first event in a data window. For data windows that introduce a sort order other than the order of arrival, such as the sorted data window and the time order data window, the function returns the first event at the specified position.

The prevwindow function returns property values or all event properties for all events in a data window. For data windows that introduce a sort order other than the order of arrival, such as the sorted data window and the time order data window, the function returns the event data sorted in that order, otherwise it returns the events sorted by order of arrival with the newest arriving event first.

The prevcount function returns the number of events held in a data window.

The prior function returns the property value of a prior event. The first parameter is an integer value that denotes the i-th 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. The second parameter is a property name or stream name. If specifying a property name, the function returns the property value for the prior event. If specifying a stream name, the function returns the prior event underlying object.

This example selects the value of the price property of the 2nd-prior event to the current Trade event.

select prior(2, price) from Trade

By using the stream alias in the prior function, the next example selects the trade event itself that is immediately prior to the current Trade event

select prior(1, trade) from Trade as trade

The prior function can be used on any event stream or view and does not have any specific view requirements. The function operates on the order of arrival of events by the event stream or view that provides the events.

The next statement uses a time batch window to compute an average volume for 1 minute of Trade events, posting results every minute. The select-clause employs the prior function to select the current average and the average before the current average:

select average, prior(1, average) 
    from TradeAverages.win:time_batch(1 min).stat:uni(volume)

The typeof function, when parameterized by a stream name, returns the event type name of the evaluated event which can be useful with variant streams. When parameterized by an expression or property name, the function returns the type name of the expression result or null if the expression result is null.

In summary, the function determines the return type of the expression at runtime by evaluating the expression and returns the type name of the expression result.

The typeof function is often used in conjunction with variant streams. A variant stream is a predefined stream into which events of multiple disparate event types can be inserted. The typeof function, when passed a stream name alias, returns the name of the event type of each event in the stream.

The following example elaborates on the use of variant streams with typeof. The first statement declares a variant stream SequencePatternStream:

create variant schema SequencePatternStream as *

The next statement inserts all order events and is followed by a statement to insert all product events:

insert into SequencePatternStream select * from OrderEvent;

insert into SequencePatternStream select * from PriceEvent;

This example statement returns the event type name for each event in the variant stream:

select typeof(sps) from SequencePatternStream as sps

The next example statement detects a pattern by utilizing the typeof function to find pairs of order event immediately followed by product event:

select * from SequencePatternStream match_recognize(
  measures A as a, B as b
  pattern (A B)
  define A as typeof(A) = "OrderEvent",
         B as typeof(B) = "ProductEvent"
  )

When passing a property name to the typeof function, the function evaluates whether the property type is event type (a fragment event type). If the property type is event type, the function returns the type name of the event in the property value or null if not provided. If the property type is not event type, the function returns the simple class name of the property value.

When passing an expression to the typeof function, the function evaluates the expression and returns the simple class name of the expression result value or null if the expression result value is null.

This example statement returns the simple class name of the value of the dynamic property prop of events in stream MyStream, or a null value if the property is not found for an event or the property value itself is null:

select typeof(prop?) from MyStream

When using subclasses or interface implementations as event classes or when using Map-event type inheritance, the function returns the event type name provided when the class or Map-type event was registered, or if the event type was not registered, the function returns the fully-qualified class name.