This topic applies only to the IBM Business Process Manager Advanced configuration.

Filters and selection criteria of query tables

Filters and selection criteria are defined during query table development using the Query Table Builder, which uses a syntax similar to SQL WHERE clauses. Use these clearly defined filters and selection criteria to specify conditions that are based on attributes of query tables.

For information about installing the Query Table Builder, see the SupportPacs site. Look for PA71 WebSphere Process Server - Query Table Builder. To access the link, see the related references section of this topic.

Attributes

Attributes used in an expression refer to query table attributes. Depending on the location of the expression, different attributes are available. For the client developer, query filters passed to the query table API are the only location where expressions can be used. For developers of composite query tables, various other locations exist where expressions can be used. The following table describes the attributes that are available at the different locations.

Table 1. Attributes for query table expressions
Where	Expression	Available attributes
Query table API	Query filter	All attributes defined on the query table. If instance-based authorization is used, all attributes defined on the WORK_ITEM query tables, prefixed with `'WI.'`. Examples: `STATE=STATE_READY`, if the query table contains a STATE attribute and if a STATE_READY constant is defined for this attribute. `STATE=STATE_READY AND WI.REASON=REASON_POTENTIAL_OWNER`, if the query table contains a STATE attribute and the query table uses instance-based authorization.
Composite query table	Query table filter
	Primary query table filter	All attributes defined for the primary query table. Example: `STATE=STATE_READY`, if the query table contains a STATE attribute and a STATE_READY constant is defined for this attribute.
	Authorization filter	All attributes defined on the WORK_ITEM predefined query table, prefixed with `'WI.'`. Example: `WI.REASON=REASON_POTENTIAL_OWNER`
	Selection criterion	All attributes defined on the related attached query table. Example: `LOCALE='en_US'`, if the attached query table contains a LOCALE attribute, such as the TASK_DESC query table.
	Selection criterion defined for supplemental query tables (with the optimizeForFiltering option enabled)	All attributes defined on the supplemental query table. Example: `VALUE='xyz'`, if the attached supplemental query table contains an attribute with this value.

The following figure shows the various locations of filters and selection criteria in expressions, and includes examples:

Figure 1. Filters and selection criteria in expressions

Expressions

Expressions have the following syntax:

expression  ::=  attribute binary_op value |
                 attribute unary_op |
                 attribute list_op list |
                 (expression) |
                 expression AND expression |
                 expression> OR expression

The following rules apply:

AND takes precedence over OR. Subexpressions are connected using AND and OR.
Brackets can be used to group expressions and must be balanced.

Examples:

STATE = STATE_READY
NAME IS NOT NULL
STATE IN (2, 5, STATE_FINISHED)
((PRIORITY=1) OR (WI.REASON=2)) AND (STATE=2)

An expression is executed in a certain scope which determines the attributes that are valid for the expression. Selection criteria, or query filters, are run in the scope of the query table on which the query is run.

The following example is for a query that is run on the predefined TASK query table:

'(STATE=STATE_READY AND WI.REASON=REASON_POTENTIAL_OWNER) 
OR (WI.REASON=REASON_OWNER)'

Binary operators

The following binary operators are available:

binary_op ::= = | < | > | <> | <= | >= | LIKE | NOT LIKE

The following rules apply:

The left-side operand of a binary operator must reference an attribute of a query table.
The right-side operand of a binary operator must be a literal value, constant value, or parameter.
The LIKE and NOT LIKE operators are only valid for attributes of attribute type STRING.
The left-side operand and the right-side operand must be of compatible attribute types.
User parameters must be compatible to the attribute type of the left-side attribute.

Examples:

STATE > 2
NAME LIKE 'start%'
STATE <> PARAM(theState)

Unary operators

The following unary operators are available:

unary_op ::= IS NULL | IS NOT NULL

The following rules apply:

The left-side operand of a unary operator must reference an attribute of a query table. Valid attributes depend on the location of the filter or selection criterion.
All attributes can be checked for null values, for example: CUSTOMER IS NOT NULL.

Example:

DESCRIPTION IS NOT NULL

List operators

The following list operators are available:

list_op ::= IN | NOT IN

The following rules apply:

The right-side of a list operator must not be replaced by a user parameter.
User parameters can be used within the list on the right-side operand.

Example:

STATE IN (STATE_READY, STATE_RUNNING, PARAM(st), 1)

Lists are represented as follows:

list ::= value [, list]

The following rules apply:

The right-side of a list operator must not be replaced by a user parameter.
User parameters can be used within the list on the right-side operand.

Examples:

(2, 5, 8)
(STATE_READY, STATE_CLAIMED)

Values

In expressions, a value is one of the following types:

Constant: A constant value, which is defined for the attribute of a predefined query table. For example, STATE_READY is defined for the STATE attribute of the TASK query table.
Literal: Any hardcoded value.
Parameter: A parameter is replaced when the query is run with a specific value.

Constants are available for some attributes of predefined query tables. For information about constants that are available on attributes of predefined query tables, refer to the information about predefined views. Only constants that define integer values are exposed with query tables. Also, instead of constants, related literal values, or parameters can be used.

Examples:

STATE_READY on the STATE attribute of the TASK query table can be used in a filter to check whether the task is in the ready state.
REASON_POTENTIAL_OWNER on the REASON attribute of the WORK_ITEM query table can be used in a filter to check whether the user who runs the query against a query table is a potential owner.
Query filter STATE=STATE_READY is the same as STATE=2, if the query is run on the TASK query table.

Literals can also be used in expressions. A special syntax must be used for timestamps and for IDs.

Examples:

STATE=1
NAME='theName'
CREATED > TS ('2008-11-26 T12:00:00')
TKTID=ID('_TKT:801a011e.9d57c52.ab886df6.1fcc0000')

Parameters in expressions allow for a dynamicity of composite query tables. There are user parameters and system parameters:

User parameters are specified using PARAM (name). This parameter must be provided when the query is run. It is passed as an instance of the com.ibm.bpe.api.Parameter class into the query table API.
System parameters are parameters that are provided at run time, without being specified when the query is run. The system parameters $USER and $LOCALE are available.
- $USER, which is a string, contains the value of the user who runs the query.
- $LOCALE, which is a string, contains the value of the locale that is used when the query is run. An example for the value of $LOCALE is 'en_US'.

You can specify a parameter in the selection criteria of an attached query table which selects on a specific locale. For example, if the primary query table is TASK in a composite query table and an attached query table is TASK_DESC. The following list contains examples of parameters:

STATE=PARAM(theState)
LOCALE=$LOCALE
OWNER=$USER