SELECT is a complex function that combines, filters, and reformats
complex message and database data.
- Source is a reference to a field, field array, or database table
(see ESQL field references)
- Alias is a temporary variable name
- Condition is any boolean expression
- Target is a relative path
SELECT
combines, filters, and reformats complex message and database data. It is
the normal method of message transformation.
There is no syntactic or
semantic difference between a SELECT operating on a database table and one
operating on a message. In particular:
- The SELECT clause expressions can use any of the broker's operators and
functions in any combination. They can refer to the tables' columns, message
fields, correlation names declared by containing SELECTs, and any other declared
variables or constants within scope.
- Their corresponding AS clauses can contain multipart paths. For example,
A.B.C, including field type specifiers such as A.B.(XML.Attribute)C), name
expressions (eg A.B.{var}), and indices (eg A.B.C[i]).
Any expressions
in these paths can use any of the broker's operators and functions in any
combination. The expressions can refer to the tables' columns, message fields,
correlation names declared by containing SELECTs, and any declared variables
or constants.
- The FROM clause can contain multiple database references, multiple message
references, or a mixture of the two. You can join tables with tables, messages
with messages, or tables with messages, with the following restrictions:
- The database FROM clause expressions, for example, Database.Schema1.Table1
cannot contain expressions of any kind. You cannot calculate a schema or table
name at run time.
- If the FROM clause refers to both messages and tables, the tables must precede
the messages.
- The WHERE clause expression can also use any of the broker's operators
and functions in any combination. The expressions can refer to the tables'
columns, message fields, correlation names declared by containing SELECTs,
and any declared variables or constants. However, be aware of the following:
- The broker treats WHERE clause expressions by examining the expression
and deciding whether the whole expression can be evaluated by the database.
If
it can, it is given to the database. In order to be evaluated by the database,
it must use only those functions and operators supported by the database.
It
can, however, still refer to message fields, correlation names declared
by containing SELECTs, and any other declared variables or constants within
scope.
- If the whole expression cannot be evaluated by the database, the broker
looks for top level AND operators and examines each subexpression separately.
It
then attempts to give the database those sub-expressions that it can evaluate,
leaving the broker to evaluate the rest.
You need to be aware of this situation for two reasons:
- Apparently trivial changes to WHERE clause expressions can have large
effects on performance. You can determine how much of the expression was
given to the database by examining a user trace.
- Some databases' functions exhibit subtle differences of behavior from
those of the broker.
Use SELECT to:
- Comprehensively reformat messages
- Access database tables
- Make an output array that is a subset of an input array
- Make an output array that contains just the values of an input array
- Count the number of entries in an array
- Select the minimum or maximum value from a number of entries in an array
- Sum the values in an array
ESQL SELECT differs from database SQL SELECT in the following
ways:
- ESQL can produce tree structured result data
- ESQL can accept arrays in SELECT clauses
- ESQL has THE and ITEM
- ESQL has no SELECT ALL
- ESQL has no SELECT DISTINCT
- ESQL has no GROUP BY or HAVING
- ESQL has no AVG column function
For information about using the SELECT function, see below.