A named window may also decorate an event to preserve original events as described in Section 5.10.4, “Decorated Events” and Section 6.2.2.1, “Named Windows Holding Decorated Events”.

To tune subquery performance when the subquery selects from a named window, consider the hints discussed in Section 5.11.8, “Hints Related to Subqueries”.

As a general rule-of-thumb, if you need to share a data window between statements, the named window is the right approach. If however rows are organized by primary key or hold aggregation state, a table may be preferable. EPL statements allow the combined use of both.

// Declare a table to hold a Count-min sketch approximate count per feed
create table AppoximateCountPerWord (feed string, approx countMinSketch())

// Declare a named window to hold 10 seconds of stock tick events
create window TenSecOfTicksWindow#time(10 sec) as StockTickEvent

select count(*) from TenSecOfTicksWindow

// does not continously output for table changes
select * from AppoximateCountPerWord

The create window statement creates a named window by specifying a window name and one or more data windows, as well as the type of event to hold in the named window.

There are two syntaxes for creating a named window: The first syntax allows modeling a named window after an existing event type or an existing named window. The second syntax is similar to the SQL create-table syntax and provides a list of column names and column types.

A new named window starts up empty. It must be explicitly inserted into by one or more statements, as discussed below. A named window can also be populated at time of creation from an existing named window.

If your application stops or destroys the statement that creates the named window, any consuming statements no longer receive insert or remove stream events. The named window can also not be deleted from after it was stopped or destroyed.

The create window statement posts to listeners any events that are inserted into the named window as new data. The statement posts all deleted events or events that expire out of the data window to listeners as the remove stream (old data). The named window contents can also be iterated on via the pull API to obtain the current contents of a named window.

[context context_name] 
		create window window_name.window_spec 
		[as] [select list_of_properties from] event_type_or_windowname
		[insert [where filter_expression]]

create window OrdersNamedWindow#keepall as OrderMapEventType

create window OrdersTimeWindow#time(30 sec) as 
		select symbol, volume, price from OrderEvent

create window OrdersTimeWindow#time(30 sec) as 
  select symbol as sym, volume as vol, price, 1 as alertId from OrderEvent

[context context_name] 
create window window_name.window_spec [as] (column_name column_type 
  [,column_name column_type [,...])

create window SecurityEvent#time(30 sec) 
(ipAddress string, userId String, numAttempts int, properties String[])

create schema SecurityData (name String, roles String[])

create window SecurityEvent#time(30 sec) 
    (ipAddress string, userId String, secData SecurityData, historySecData SecurityData[])

@EventRepresentation(objectarray) create window FooWindow#time(5 sec) as (string prop1)

create window ScratchOrders#keepall as OrdersNamedWindow insert

create window ScratchBuyOrders#time(10) as OrdersNamedWindow insert where side = 'buy'

For inserting into a named window and for simultaneously checking if the inserted row already exists in the named window or for atomic update-insert operation on a named window, or for targeting a specific partition, you must use on-merge as described in Section 6.8, “Triggered Upsert Using the On-Merge Clause”. On-merge is similar to the SQL merge clause and provides what is known as an "Upsert" operation: Update existing events or if no existing event(s) are found then insert a new event, all in one atomic operation provided by a single EPL statement.

This example first creates a named window using some of the columns of an OrderEvent event type:

create window OrdersWindow#keepall as select symbol, volume, price from OrderEvent

The insert into the named window selects individual columns to be inserted:

insert into OrdersWindow(symbol, volume, price) select name, count, price from FXOrderEvent

An alternative form is shown next:

insert into OrdersWindow select name as symbol, vol as volume, price from FXOrderEvent

Following above statement, the engine enters every FXOrderEvent arriving into the engine into the named window 'OrdersWindow'.

The following EPL statements create a named window for an event type backed by a Java class and insert into the window any 'OrderEvent' where the symbol value is IBM:

create window OrdersWindow#time(30) as com.mycompany.OrderEvent

insert into OrdersWindow select * from com.mycompany.OrderEvent(symbol='IBM')

The last example adds one column named 'derivedPrice' to the 'OrderEvent' type by specifying a wildcard, and uses a user-defined function to populate the column:

create window OrdersWindow#time(30) as select *, price as derivedPrice from OrderEvent

insert into OrdersWindow select *, MyFunc.func(price, percent) as derivedPrice from OrderEvent

Event representations based on Java base classes or interfaces, and subclasses or implementing classes, are compatible as these statements show:

// create a named window for the base class
create window OrdersWindow#unique(name) as select * from ProductBaseEvent

// The ServiceProductEvent class subclasses the ProductBaseEvent class
insert into OrdersWindow select * from ServiceProductEvent

// The MerchandiseProductEvent class subclasses the ProductBaseEvent class
insert into OrdersWindow select * from MerchandiseProductEvent

To avoid duplicate events inserted in a named window and atomically check if a row already exists, use on-merge as outlined in Section 6.8, “Triggered Upsert Using the On-Merge Clause”. An example:

on ServiceProductEvent as spe merge OrdersWindow as win
where win.id = spe.id when not matched then insert select *

create window OrdersWindow#time(30) as select *, price as priceTotal from OrderEvent

insert into OrdersWindow select *, price * unit as priceTotal from ServiceOrderEvent

The create table statement creates a table.

A new table starts up empty. It must be explicitly aggregated-into using into table, or populated by an on-merge statement, or populated by insert into.

The syntax for creating a table provides the table name, lists column names and types and designates primary key columns:

[context context_name] 
create table table_name [as] (column_name column_type [primary key]
  [,column_name column_type [primary key] [,...]])

The table_name you assign to the table can be any identifier. The name should not already be in use as an event type or named window name.

You may refer to a context by specifying the context keyword followed by a context name. Contexts are described in more detail at Chapter 4, Context and Context Partitions. The effect of referring to a context is that your table operates according to the context dimensional information as declared for the context. For usage and limitations please see the respective chapter.

The column_name is an identifier providing the column name.

The column_type is required for each column. There are two categories of column types:

After each column type you may add the primary key keywords. This keyword designates the column as a primary key. When multiple columns are designated as primary key columns the combination of column values builds a compound primary key. The order in which the primary key columns are listed is important.

The next statement creates a table to hold a numAttempts count aggregation state and a column named active of type boolean, per ipAddress and userId:

create table SecuritySummaryTable (
  ipAddress string primary key,
  userId String primary key, 
  numAttempts count(*),
  active boolean)

The example above specifies ipAddress and userId as primary keys. This instructs the engine that the table holds a single row for each distinct combination of ipAddress and userId. The two values make up the compound key and there is a single row per compound key value.

If you do not designate any columns of the table as a primary key column, the table holds only one row (or no rows).

The create table statement does not provide output to its listeners. The table contents can be iterated on via the pull API to obtain the current contents of a table.

create table MyStats (
  myKey string primary key,
  myAvedev avedev(int), // column holds a mean deviation of int-typed values
  myAvg avg(double), // column holds an average of double-typed values
  myCount count(*), // column holds a count
  myMax max(int), // column holds a highest int-typed value
  myMedian median(float), // column holds the median of float-typed values
  myStddev stddev(java.math.BigDecimal), // column holds a standard deviation of BigDecimal values
  mySum sum(long), // column holds a sum of long values
  myFirstEver firstever(string), // column holds a first-ever value of type string
  myCountEver countever(*) // column holds the count-ever (regardless of data windows)
)

create table MyStatsMore (
  myKey string primary key,
  myAvgFiltered avg(double, boolean), // column holds an average of double-typed values
                      // and filtered by a boolean expression to be provided
  myAvgDistinct avg(distinct double) // column holds an average of distinct double-typed values
)

create table MyEventAggregationTable (
  myKey string primary key,
  myWindow window(*) @type(MyEvent), // column holds a window of MyEvent events
  mySorted sorted(mySortValue) @type(MyEvent), // column holds MyEvent events sorted by mySortValue
  myMaxByEver maxbyever(mySortValue) @type(MyEvent) // column holds the single MyEvent event that 
        // provided the highest value of mySortValue ever
)

create table MyStatsCustom (myCustom myAggregation('some code', 100))

Use the into table keywords to instruct the engine to aggregate into table columns. A given statement can only aggregate into a single table.

For example, consider a table that holds the count of intrusion events keyed by the combination of from-address and to-address:

create table IntrusionCountTable (
  fromAddress string primary key,
  toAddress string primary key,
  countIntrusion10Sec count(*),
  countIntrusion60Sec count(*)
)

The next sample statement updates the count considering the last 10 seconds of events:

into table IntrusionCountTable
select count(*) as countIntrusion10Sec
from IntrusionEvent#time(10)
group by fromAddress, toAddress

Multiple statements can aggregate into the same table columns or different table columns. The co-aggregating ability allows you to co-locate aggregation state conveniently.

The sample shown below is very similar to the previous statement except that it updates the count considering the last 60 seconds of events:

into table IntrusionCountTable
select count(*) as countIntrusion60Sec
from IntrusionEvent#time(60)
group by fromAddress, toAddress

Considering the example above, when an intrusion event arrives and a row for the group-by key values (from and to-address) does not exists, the engine creates a new row and updates the aggregation-type columns. If the row for the group-by key values exists, the engine updates the aggregation-type columns of the existing row.

Tables can have no primary key columns. In this case a table either has a single row or is empty.

The next two EPL statements demonstrate table use without a primary key column:

create table TotalIntrusionCountTable (totalIntrusions count(*))

into table TotalIntrusionCountTable select count(*) as totalIntrusions from IntrusionEvent

In conjunction with into table the unidirectional keyword is not supported.

create table MyTable (theKey long primary key, theCount count(*))

// Invalid: No group-by clause however the table declares a primary key
into table MyTable select count(*) as theCount from MyEvent

// Invalid: Two expressions in the group-by clause however the table declares a single primary key
into table MyTable select count(*) as theCount from MyEvent group by longPropertyOne, longPropertyTwo

// Invalid: The group-by clause expression returns a string-typed value however the table expects a long-type primary key
into table MyTable select count(*) as theCount from MyEvent group by stringProperty

create table MyTable (theKey long primary key, theCount count(*))

// Invalid: the sum aggregation state is not available in a table column
into table MyTable select count(*) as theCount, sum(intProperty) from MyEvent group by longProperty

create table MyTable (theKey long primary key, theCount count(*))

// Invalid: the sum aggregation state is not compatible with count(*) that was declared for the table column's type
into table MyTable select sum(intProperty) as theCount from MyEvent group by longProperty

expression alias totalIntrusions {count(*)}
select totalIntrusions from IntrusionEvent

For accessing table columns by primary key, EPL provides a convenient syntax that allows you to read table column values simply by providing the table name, primary key value expressions (if required by the table) and the column name.

The synopsis for table-column access expressions is:

table-name[primary_key_expr [, primary_key_expr] [,...]][.column-name]

The expression starts with the table name. If the table declares primary keys you must provide the primary_key_expr value expressions for each primary key within square brackets. To access a specific column, add the (.) dot character and the column name.

For example, consider a table that holds the count of intrusion events keyed by the combination of from-address and to-address:

create table IntrusionCountTable (
  fromAddress string primary key,
  toAddress string primary key,
  countIntrusion10Sec count(*)
)

Assuming that a FireWallEvent has string-type properties named from and to, the next EPL statement outputs the current 10-second intrusion count as held by the IntrusionCountTable row for the matching combination of keys:

select IntrusionCountTable[from, to].countIntrusion10Sec from FirewallEvent

The number of primary key expressions, the return type of the primary key expressions and the order in which they are provided must match the primary key columns that were declared for the table. If the table does not have any primary keys declared, you cannot provide any primary key expressions.

If a row for the primary key (or compound key) cannot be found, the engine returns a null value.

An example table without primary key columns is shown next:

create table TotalIntrusionCountTable (totalIntrusions count(*))

A sample statement that outputs the current total count every 60 seconds is:

select TotalIntrusionCountTable.totalIntrusions from pattern[every timer:interval(60 sec)]

Table access expressions can be used anywhere in statements except as parameter expressions for data windows, the update istream, context declarations, output limit expressions, pattern observer and guard parameters, pattern every-distinct, pattern match-until bounds, pattern followed-by max and create window insert or select expression and as a create variable assignment expression.

select IntrusionCountTable[from, to] from FirewallEvent

create table MyTable (
  theWindow window(*) @type(MyEvent),
  theSorted sorted(mySortValue) @type(MyEvent)
)

select MyTable.theWindow.first(), MyTable.theSorted.maxBy() from SomeOtherEvent

For inserting into a table and for simultaneously checking if the inserted row already exists in the table or for atomic update-insert operation on a table, or for targeting a specific partition, you must use on-merge as described in Section 6.8, “Triggered Upsert Using the On-Merge Clause”. On-merge is similar to the SQL merge clause and provides what is known as an "Upsert" operation: Update existing rows or if no existing rows(s) are found then insert a new row, all in one atomic operation provided by a single EPL statement.

The following statement populates the example table declared earlier:

insert into IntrusionCountTable select fromAddress, toAddress from FirewallEvent

Note that when a row with the same primary key values already exists, your statement may encounter a unique index violation at runtime. If the inserted-into table does not have primary key columns, the table holds a maximum of one row and your statement may also encounter a unique index violation upon attempting to insert a second row. Use on-merge to prevent inserts of duplicate rows.

Table columns that are aggregation functions cannot be inserted-into and must be updated using into table instead.

You may also explicitly list column names as discussed earlier in Section 6.2.2, “Inserting Into Named Windows”. For insert-into, the context name must be the same context name as declared for the create table statement or the context name must be absent for both.

For correlated queries that correlate triggering events with rows held by a named window, Esper internally creates efficient indexes to enable high performance querying of rows. It analyzes the where clause to build one or more indexes for fast lookup in the named window based on the properties of the triggering event.

To trigger an on-select when an update to the selected named window occurs or when the triggering event is the same event that is being inserted into the named window, specify the named window name as the event type.

The next query fires the select for every change to the named window OrdersNamedWindow:

on OrdersNamedWindow as trig 
select onw.symbol, sum(onw.volume) 
from OrdersNamedWindow as onw 
where onw.symbol = trig.symbol

For named windows, the iterator of the EPStatement object representing the on select clause returns the last batch of selected events in response to the last triggering event, or null if the last triggering event did not select any rows.

For tables, the iterator of the EPStatement object representing the on select clause returns no events.

For correlated queries that correlate triggering events with rows held by a table, the engine utilizes either primary key columns or secondary explicitly-created indexes to enable high performance querying of rows, based on an analysis of the where clause.

The similarities and differences between an on select clause and a regular or outer join (and not unidirectional) are as follows:

On-select and the unidirectional join can be compared as follows.

On-select, on-merge, on-insert, on-delete, on-update and on-select-and-delete operate only on named windows or tables. Unidirectional joins however can operate on any stream. If the unidirectional join is between a single named window or table and a triggering event or pattern and that triggering event or pattern is marked unidirectional, the unidirectional join is equivalent to on-select.

A unidirectional join does not execute under a named window context partition lock and instead is a consumer relationship to the named window.

Statements that reference the named window receive the new event in the insert stream and the event prior to the update in the remove stream.

For correlated queries (as above) that correlate triggering events with events held by a named window, Esper internally creates efficient indexes to enable high performance update of events.

The iterator of the EPStatement object representing the on update clause can also be helpful: It returns the last batch of updated events in response to the last triggering event, in any order, or null if the last triggering event did not update any rows.

On-Update may not update primary key columns.

For correlated queries that correlate triggering events with rows held by a table, the engine utilizes either primary key columns or secondary explicitly-created indexes to enable high performance querying of rows, based on an analysis of the where clause.

The iterator of the EPStatement object representing the on update clause does not return any rows.

By means of patterns the on delete clause and on select clause (described below) can look for more complex conditions to occur, possibly involving multiple events or the passing of time. The syntax for on delete with a pattern expression is show next:

on pattern [pattern_expression] [as stream_name]
delete from window_or_table_name [as stream_name]
[where criteria_expression]

The pattern_expression is any pattern that matches zero or more arriving events. Tags can be used to name events in the pattern and can occur in the optional where clause to correlate to events to be removed from a named window.

In the next example the triggering pattern fires every 10 seconds. The effect is that every 10 seconds the statement removes all rows from the SecuritySummaryTable table:

on pattern [every timer:interval(10 sec)] delete from SecuritySummaryTable

The following example shows the use of tags in a pattern and executes against the OrdersNamedWindow named window instead:

on pattern [every ord=OrderEvent(volume>0) or every flush=FlushOrderEvent] 
delete from OrdersNamedWindow as win
where ord.id = win.id or flush.id = win.id

The pattern above looks for OrderEvent events with a volume value greater then zero and tags such events as 'ord'. The pattern also looks for FlushOrderEvent events and tags such events as 'flush'. The where clause deletes from the OrdersNamedWindow named window any rows that match in the value of the 'id' property either of the arriving events.

Statements that reference the named window receive the deleted event as part of the remove stream.

For correlated queries (as above) that correlate triggering events with rows held by a named window, Esper internally creates efficient indexes to enable high performance deletion of rows.

The iterator of the EPStatement object representing the on update clause can also be helpful: It returns the last batch of deleted rows in response to the last triggering event, in any order, or null if the last triggering event did not update any rows.

For correlated queries that correlate triggering events with rows held by a table, the engine utilizes either primary key columns or secondary explicitly-created indexes to enable high performance querying of rows, based on an analysis of the where clause.

The iterator of the EPStatement object representing the on delete clause does not return any rows.

Use on merge with insert to inserts one row. This syntax is:

on event_type[(filter_criteria)] [as stream_name]
merge [into] window_or_table_name [as stream_name]
insert [ (property_name [, property_name] [,...]) ] select select_expression [, select_expression[,...]]

The event_type is the name of the type of events that trigger the merge. It is optionally followed by filter_criteria which are filter expressions to apply to arriving events. The optional as keyword can be used to assign a name for use in the where clause. Patterns and named windows can also be specified in the on clause as described in prior sections.

The window_or_table_name is the name of the named window to insert a row into. The as keyword is also available to assign a name to the named window or table.

It follows the insert keyword and optionally the into keyword. Optionally you can provide a list of property names in parenthesis.

It follows the required select keyword and one or more select-clause expressions. The wildcard (*) is available in the select-clause as well.

On-merge can be used with tables and named windows. This examples uses the SecuritySummaryTable table that was defined earlier.

This example statement inserts a row into the SecuritySummaryTable table when a SecuritySummary arrives:

on SecuritySummary merge SecuritySummaryTable insert select ipAddress, userId

The following EPL is equivalent to the query above and uses the upsert syntax instead, described next.

on SecuritySummary merge SecuritySummaryTable where 1=2 when not matched then insert select ipAddress, userId

The syntax for on merge for update-insert (upsert) is as follows:

on event_type[(filter_criteria)] [as stream_name]
merge [into] window_or_table_name [as stream_name]
[where criteria_expression]
  when [not] matched [and search_condition]
    then [
      insert [into streamname]
          [ (property_name [, property_name] [,...]) ] 
          select select_expression [, select_expression[,...]]
          [where filter_expression]
      |
      update set mutation_expression [, mutation_expression [,...]]
          [where filter_expression]
      |
      delete
          [where filter_expression]
    ]
    [then [insert|update|delete]] [,then ...]
  [when ...  then ... [...]] 

The event_type is the name of the type of events that trigger the merge. It is optionally followed by filter_criteria which are filter expressions to apply to arriving events. The optional as keyword can be used to assign a name for use in the where clause. Patterns and named windows can also be specified in the on clause as described in prior sections.

The window_or_table_name is the name of the named window or table to insert, update or delete rows. The as keyword is also available to assign a name to the named window or table.

The optional where clause contains a criteria_expression that correlates the arriving (triggering) event to the rows to be considered of the table or named window. We recommend specifying a criteria expression that is as specific as possible.

Following the where clause is one or more when matched or when not matched clauses in any order. Each may have an additional search condition associated.

After each when [not] matched follow one or more then clauses that each contains the action to take: Either an insert, update or delete keyword.

After when not matched only insert action(s) are available. After when matched any insert, update and delete action(s) are available.

After insert follows, optionally, the into keyword followed by the stream name or named window to insert-into. If no into and stream name is specified, the insert applies to the current table or named window. It follows an optional list of columns inserted. It follows the required select keyword and one or more select-clause expressions. The wildcard (*) is available in the select-clause as well. It follows an optional where-clause that may return Boolean false to indicate that the action should not be applied.

After update follows the set keyword and one or more mutation expressions. For mutation expressions please see Section 6.6, “Updating Data: The On Update Clause”. It follows an optional where-clause that may return Boolean false to indicate that the action should not be applied.

After delete follows an optional where-clause that may return Boolean false to indicate that the action should not be applied.

When according to the where-clause criteria_expression the engine finds no rows in the named window or table that match the condition, the engine evaluates each when not matched clause. If the optional search condition returns true or no search condition was provided then the engine performs all of the actions listed after each then.

When according to the where-clause criteria_expression the engine finds one or more rows in the named window or table that match the condition, the engine evaluates each when matched clause. If the optional search condition returns true or no search condition was provided the engine performs all of the actions listed after each then.

The engine executes when matched and when not matched in the order specified. If the optional search condition returns true or no search condition was specified then the engine takes the associated action (or multiple actions for multiple then keywords). When the block of actions completed the engine proceeds to the next matching row, if any. After completing all matching rows the engine continues to the next triggering event if any.

On-merge can be used with tables and named windows. The examples herein declare a ProductWindow named window and also use the SecuritySummaryTable table to provide examples for each.

This example statement updates the SecuritySummaryTable table when a ResetEvent arrives setting the active column's value to false:

on ResetEvent merge SecuritySummaryTable
when matched and active = true then update set active = false

A longer example utilizing a named window follows. You start by declaring a schema that provides a product id and that holds a total price:

create schema ProductTotalRec as (productId string, totalPrice double)

We create a named window that holds a row for each unique product:

create window ProductWindow#unique(productId) as ProductTotalRec

The events for this example are order events that hold an order id, product id, price, quantity and deleted-flag declared by the next schema:

create schema OrderEvent as (orderId string, productId string, price double, 
    quantity int, deletedFlag boolean)

The following EPL statement utilizes on-merge to total up the price for each product based on arriving order events:

on OrderEvent oe
  merge ProductWindow pw
  where pw.productId = oe.productId
  when matched
    then update set totalPrice = totalPrice + oe.price
  when not matched 
    then insert select productId, price as totalPrice

In the above example, when an order event arrives, the engine looks up in the product named window the matching row or rows for the same product id as the arriving event. In this example the engine always finds no row or one row as the product named window is declared with a unique data window based on product id. If the engine finds a row in the named window, it performs the update action adding up the price as defined under when matched. If the engine does not find a row in the named window it performs the insert action as defined under when not matched, inserting a new row.

The insert keyword may be followed by a list of columns as shown in this EPL snippet:

// equivalent to the insert shown in the last 2 lines in above EPL
...when not matched 
    then insert(productId, totalPrice) select productId, price

The second example demonstrates the use of a select-clause with wildcard, a search condition and the delete keyword. It creates a named window that holds order events and employs on-merge to insert order events for which no corresponding order id was found, update quantity to the quantity provided by the last arriving event and delete order events that are marked as deleted:

create window OrderWindow#keepall as OrderEvent

on OrderEvent oe
  merge OrderWindow pw
  where pw.orderId = oe.orderId
  when not matched 
    then insert select *
  when matched and oe.deletedFlag=true
    then delete
  when matched
    then update set pw.quantity = oe.quantity, pw.price = oe.price

In the above example the oe.deletedFlag=true search condition instructs the engine to take the delete action only if the deleted-flag is set.

You may specify multiple actions by providing multiple then keywords each followed by an action. Each of the insert, update and delete actions can itself have a where-clause as well. If a where-clause exists for an action, the engine evaluates the where-clause and applies the action only if the where-clause returns Boolean true.

This example specifies two update actions and uses the where-clause to trigger different update behavior depending on whether the order event price is less than zero. This example assumes that the host application defined a clearorder user-defined function, to demonstrate calling a user-defined function as part of the update mutation expressions:

on OrderEvent oe
  merge OrderWindow pw
  where pw.orderId = oe.orderId
  when matched
    then update set clearorder(pw) where oe.price < 0
    then update set pw.quantity = oe.quantity, pw.price = oe.price where oe.price >= 0

To insert events into another stream and not the named window, use insert into streamname.

In the next example each matched-clause contains two actions, one action to insert a log event and a second action to insert, delete or update:

on OrderEvent oe
  merge OrderWindow pw
  where pw.orderId = oe.orderId
  when not matched 
    then insert into LogEvent select 'this is an insert' as name
    then insert select *
  when matched and oe.deletedFlag=true
    then insert into LogEvent select 'this is a delete' as name
    then delete
  when matched
    then insert into LogEvent select 'this is a update' as name
    then update set pw.quantity = oe.quantity, pw.price = oe.price

While the engine evaluates and executes all actions listed under the same matched-clause in order, you may not rely on updated field values of an earlier action to trigger the where-clause of a later action. Similarly you should avoid simultaneous update and delete actions for the same match: the engine does not guarantee whether the update or the delete take final affect.

Your application can subscribe a listener to on merge statements to determine inserted, updated and removed events. Statements post any events that are inserted to, updated or deleted from a named window to all listeners attached to the statement as new data and removed data.

The following limitations apply to on-merge statements:

Statements that reference the named window receive an insert and remove stream represening the insertions, changes and deletions to named window rows.

For correlated queries (as above) that correlate triggering events with rows held by a named window, Esper internally creates efficient indexes to enable high performance update and removal of events especially from named windows that hold large numbers of events.

Upon iteration, the statement provides the last inserted events, if any.

On-Merge may not update primary key columns.

For correlated queries that correlate triggering events with rows held by a table, the engine utilizes either primary key columns or secondary explicitly-created indexes to enable high performance querying of rows, based on an analysis of the where clause.

The iterator of the EPStatement object representing the on merge clause does not return any rows.

Your application can insert rows into a table or named window using on-demand (fire-and-forget, non-continuous) queries as described in Section 14.5, “On-Demand Fire-and-Forget Query Execution”.

The engine allows the standard SQL syntax and values keyword and also supports using select to provide values.

The syntax using the values keyword is:

insert into window_or_table_name [(property_names)]
values (value_expressions)

The syntax using select is as follows:

insert into window_or_table_name [(property_names)]
select value_expressions

The window_or_table_name is the name of the table or named window to insert rows into.

After the named window or table name you can optionally provide a comma-separated list of property names.

When providing property names, the order of value expressions in the values list or select clause must match the order of property names specified. Column names provided in the select-clause, if specified, are ignored.

When not providing property names and when specifying the values keyword, the order of values must match the order of properties declared for the named window or table. When not providing property names and when specifying the select-clause, expressions must name the properties to be inserted into by assigning a column name using the as keyword.

The example code snippet inserts a new order row into the OrdersWindow named window:

String query = 
  "insert into OrdersWindow(orderId, symbol, price) values ('001', 'GE', 100)";
epService.getEPRuntime().executeQuery(query);

Instead of the values keyword you may specify a select-clause as this example shows:

String query = 
  "insert into OrdersWindow(orderId, symbol, price) select '001', 'GE', 100";
epService.getEPRuntime().executeQuery(query);

The following EPL inserts the same values as above but specifies property names as part of the select-clause expressions:

insert into OrdersWindow
select '001' as orderId, 'GE' as symbol, 100 as price

The next EPL inserts the same values as above and does not specify property names thereby populating the first 3 properties of the type of the named window:

insert into OrdersWindow values ('001', 'GE', 100)

Your application can update table and named window rows using on-demand (fire-and-forget, non-continuous) queries as described in Section 14.5, “On-Demand Fire-and-Forget Query Execution”.

The syntax for the update clause is as follows:

update window_or_table_name [as stream_name]
set mutation_expression [, mutation_expression [,...]]
[where criteria_expression]

The window_or_table_name is the name of the table or named window to remove rows from. The as keyword is also available to assign a name to the table or named window.

After the set keyword follows a comma-separated list of mutation expressions. For fire-and-forget queries the following restriction applies: Subqueries, aggregation functions and the prev or prior function may not be used in expressions. Mutation expressions are detailed in Section 6.6, “Updating Data: The On Update Clause”.

The optional where clause contains a criteria_expression that identifies rows to be updated.

The example code snippet updates those rows of the named window that have a negative value for volume:

String query = "update OrdersNamedWindow set volume = 0 where volumne = 0";
epService.getEPRuntime().executeQuery(query);

To instruct the engine to use the initial property value before update, prefix the property name with the literal initial.

Your application can delete rows from a named window or table using on-demand (fire-and-forget, non-continuous) queries as described in Section 14.5, “On-Demand Fire-and-Forget Query Execution”.

The syntax for the delete clause is as follows:

delete from window_or_table_name [as stream_name]
	[where criteria_expression]

The window_or_table_name is the name of the named window or table to delete rows from. The as keyword is also available to assign a name to the named window or table.

The optional where clause contains a criteria_expression that identifies rows to be removed from the named window or table.

The example code snippet deletes from a named window all rows that have a negative value for volume:

String query = "delete from OrdersNamedWindow where volume <= 0";
epService.getEPRuntime().executeQuery(query);