Using the Query Operator

Specifies whether this query performs a Read, Write, or Delete operation on the associated table. For Write operations, you must also specify Insert or Update on the Operation tab.

This is the primary determination of query type. Your choice in this field and on the Operation tab determine what is shown in the other fields in this tab and in other tabs in the Properties view.

The Scope control is only shown when the Operation control is set to Read or Delete.

When shown, the Scope control is dimmed and unavailable when the associated Query Table's Type control is set to On heap or On disk on its Table Settings tab.

When the Query Table's Type is In transactional memory, you can specify the Scope setting of Cluster-wide to have the Read or Delete operation apply to all instances of this Query Table in the cluster, or to the Local node only.

For a Read operation, the Where drop-down list offers the following options:

For a Delete operation, the Where drop-down list offers the same options as a Read operation, with one exception:

For a Write operation, the Where drop-down list only offers the Primary Index selection:

The Order control is only active under certain circumstances:

The control shows as Order: (Not applicable) for Read and Delete operations on Query Tables with no indexes or with all unordered (hash) indexes.

The control does not appear at all on the Output tab when the Query tab specifies a Write operation.

To use the Order control, select the By radio button. The following rules then apply to the Index drop-down list that follows the By control:

The Order control specifies the ordering of the Read or Delete operation's initial selection of rows from the Query Table. It does not rearrange the order of rows after processing by the Operation, Fallback, or Output tabs.

The selection of rows ordered by the Order control can be further limited by the Limit control. Operations are performed in the following sequence:

This sequence can be illustrated with a simple Query operator test setup configured as follows:

For Read and Delete operations, if you chose a Where setting that returns more than one row, you can specify a limit to the number of rows returned by your query. To do so, enable the Limit option and enter a StreamBase expression that evaluates to a positive integer, which is taken as the maximum number of rows to return.

The Limit option is dimmed and unavailable for write operations, or for operations where the query returns only one row, such as when matching against a primary index.

If used, the limit applies to the initial selection of rows from the associated data construct. If this Query operator uses aggregate expressions and grouping, the limit is applied before aggregation and grouping occur.

Your limit expression can be a simple integer, or a more complex expression that calculates a row limit based on conditions upstream. You can specify the row limit at runtime by using an expression such as ${RowLimit} in this field, then specifying a module parameter for the containing module named RowLimit.

When you specify the parameter in the Parameters tab of the EventFlow Editor, you can specify a default value:

Use this option to retrieve a list of the top n rows, or to maintain a Query Table by deleting n top or bottom values. With this limit set, the query ends after the specified number of rows have been returned. If fewer than the specified number of rows can be returned, the query ends without returning values for the missing rows.

See also the discussion above of the Order control that appears under certain circumstances. If used, the Order control can limit the initial selection of rows before the Limit control takes effect.

The area at the bottom of the Query tab takes on different forms, depending on the settings of the Operation and Where controls.

This section explains how to specify ranges for ordered (btree) primary or secondary index keys when using the Range Specification grid in the Query tab.

The Field Name and Type columns are filled in for you with the name and type of the table fields marked as index fields. The range specification is evaluated using the input tuple against the data stored in the Query Table. The result of the query is the ANDing of all the field range specifications. There is no support for ORing the range specifications.

Set a range for each field. You must enter an operation and an expression for each field in the index, as follows:

Including primary and secondary index fields in boundary expressions improves query efficiency. If a range expression matches fields in a table index, table scans can be avoided or bracketed. The query planner first selects matching records using the indexed fields and then scans only that subset to match the unindexed fields. For example, suppose you have a query of this type:

(NonIndexedField1 == 1 || NonIndexedField2 == 2) && (PrimaryIndexField > 10)

and in the Query operator's Query tab you set the following properties: Order by Primary Index, Limit 20.

As the table is sorted by its primary index, at runtime only those non-indexed fields having a primary key greater than 10 are scanned to match the preceding part of the predicate, and the scan halts as soon as 20 records are found.

For Read and Delete operations, use the Operation tab to specify what to do if the operation fails. For Read operations, you are specifying what to do if no matching rows were found in the query. For Delete operations, you are specifying what to do if the specified rows to be deleted were not found.

The options for this tab provide downstream components with some flexibility for detecting a Read or Delete failure in this Query operator. To detect a failure, your downstream components can test for null values in the output tuple, or for a miscount between input and output tuples, or for a particular set of values you inject into the output tuple, as required by your application design.

For both Read and Delete operations, select among the following options:

For Write operations, use the Operation tab to define what is written to the associated data construct. Write operations can be row inserts or row updates.

Each section of the Operation tab is discussed in the following subsections:

Overview of the Operation Tab

For Query Write operations, the Operation tab looks like the following example, where the incoming tuple has the schema: (Symbol, Price, Description). The Query tab specifies matching the table field Symbol against the input tuple's Symbol field.

In this example, the table fields section in the lower left shows the schema of the associated data construct, and shows what is expected to happen to each field of the selected table row for this Write-Update operation, as follows:

• The Name field was not in the input tuple, and is not otherwise specified on this tab. Thus, the table's Name field is not changed in any way by this Query operation.

• The Symbol field is the primary key, marked with the key icon. Primary keys are always included in Write-Update and Write-Insert operations.

• The Price field is to be updated from this operator's input stream. This is specified with the Input Fields: All selection.

• The Color field was not in the input tuple, but is set with a value specified in the Additional Expressions grid.

• The Description field was in the input tuple, but is overridden with an Ignore action in the Additional Expressions grid. Thus, the table's Description field is not changed by this operation.

Finally, in the upper right, the If no row is found control specifies what to do if there is no match for the expression specified on the Query tab.

Table Fields Section

The table fields section of the Operation tab shows the schema of the associated data construct. Icons and gray text explain the operation that occur for each field of the selected row.

At the top of the section is a filter field. If the associated table has a large schema, use the filter field to narrow the fields shown.

When you first open the Operation tab, the table fields section takes on different appearances, depending on upstream components:

• The Operation tab for a new Query operator that has no upstream connection shows the primary key marked with a key icon and all other fields unset:

• The default setting for the Input Fields control is All. Accordingly, if a new Query operator's incoming component has a schema that includes one or more same-named fields, then the Operation tab automatically configures each table field to be set by its corresponding input field. In the following image, the incoming stream has Symbol and Price fields:

Icons decorate table fields as follows:

Icon	Meaning
Primary key	The data construct's primary key. Primary key values are always included in Write-Update and Write-Insert operations.
Value from input stream	Table field is set with the same-named field from this operator's input stream.
Value from expression	Table field is set with an expression in the Additional Expressions grid.
Not set	Table field is not set and will not be changed by this Query operator.

The context menu in the table fields section operates on the selected row, or on the section as a whole, if no current selection.

The four context menu items at the top operate on the currently selected field in the table fields section.

Set

Copies the current table field to the Additional Expressions grid, filling in the Action and Field Name fields. Enter an expression that resolves to the field name or value you want to set for this field.

Tip

Double-click any row in the table field section as an alias for the Set context menu action.

Ignore

Copies the current table field to the grid, filling in the Ignore action for that field.

Tip

Double-click any row in the table field section to place the current table field in the Additional Expressions grid; then change the Action column from Set to Ignore.

Remove Expression

Removes from the Additional Expressions grid the row for the current table field. The grid row must be complete and must include a valid expression before this menu action works. You can also remove rows from the grid using the red X () control.

Show in Table

When the Additional Expressions grid has many entries, including some off screen, use the Show in Table menu command to jump to the grid row for the current table field.

The remaining context menu items are self-explanatory, and operate on the table fields section as a whole.

A Query operator with Write or Delete operation type can specify only simple functions in expressions in the Operation Settings and Output tabs. A typecheck error results if you attempt to use an aggregate function with Query-Write or Query-Delete.

A Query operator with Read operation type can use either simple functions or aggregate functions in expressions, but not both at the same time. The type of functions you use on the Output tab determines whether this operator emits the rows retrieved by the Read operation or emits calculated information about the rows retrieved. There are three cases:

For Query-Read operators, it is a typecheck error to try to use expressions that resolve to simple functions and expressions that resolve to aggregate functions on the same Output tab. For typechecking purposes, an expression consisting of only a field name, including a qualified field name such as input1.Price, is considered a simple function.

For example, a module might load a Query Table from a CSV file at startup, with one row for each member of the NASDAQ 100 list (as is done in the Query.sbapp sample shipped with StreamBase). For such a table:

Aggregate functions are listed on the Expression Language Functions page, starting with Aggregate Functions Overview.

The changes you specify in this tab are applied in top-down order in two ways: top to bottom in the order of grids in the tab, and top to bottom in the order of field expressions in each grid. That is, the output tuple is assembled in the following way:

If you specify one or more aggregate expressions in the Additional Expressions grid, but do not specify a Group By field, the output tuple is assembled as follows:

If you specify one or more Group By fields in the Group Options tab, the output tuple is assembled as follows:

The upper field grids in the Output tab are collapsed by default, as illustrated here:

If your goal is to specify all fields in a grid, or none, you do not need to open the field grid. Use the All or None radio buttons in the grid's label row. To specify a difference from a grid's All or None setting, open the grid and enter a field row as described below. A grid with a difference is marked in the label row as a change when the grid is closed again. In the illustration below, the Input Fields grid specifies adding all fields to the output tuple, with one difference as specified in the grid body (here unseen).

The grids that appear in the Output tab vary depending on the query operation specified in the Query tab. All operation types have Input Fields and Additional Expressions grids, but the field grids that affect data construct rows vary as shown in the following table:

The following example of an edited Input Field grid shows that all fields in the input tuple are to be included as-is, except the Description field, which is removed. For the Price field, the operator compares the value in the input tuple with the value of the same-named field in the selected table row, and places the higher of the two prices in the output tuple.

Field grids have the following editing features:

The following table describes the buttons at the top of each Field grid.

Button	Name	Description
	Add	Adds a row below the currently selected row, or to the end of the grid if no row is selected. Click the arrow on the button's right to specify whether the row should be added above or below the currently selected row. When you add a row, the newly created row is highlighted. To start entering information, click in the cell you want to edit. (Some cells are not user-editable.)
	Remove	Removes the currently selected row. Click the arrow on the button's right to remove all rows or all currently selected rows.
	Move Up, Move Down	Move Up moves the selected row up by one row. Move Down moves the selected row down by one row.
	Select Fields	Click to open the Select Fields to Replace dialog that shows available fields for the Input Fields or Table Fields grids. Click the down arrow to select the alternate actions Include or Remove, which open the Select Fields to Include and Select Fields to Remove dialogs, respectively.