SELECT function

Start of changeThe SELECT function combines, filters, and transforms complex message and database data. End of change

SYNTAX

Usage

The SELECT function is the usual and most efficient way of transforming messages. You can 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 only 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
  • 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
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.

Differences from the SQL standard

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 THE and ITEM parameters
  • ESQL has no SELECT ALL function in this release
  • Start of changeESQL has no ORDER BY function in this releaseEnd of change
  • ESQL has no SELECT DISTINCT function in this release
  • ESQL has no GROUP BY or HAVING parameters in this release
  • ESQL has no AVG column function in this release
Related concepts
ESQL overview
Related tasks
Developing ESQL
Transforming a simple XML message
Transforming a complex XML message
Returning a scalar value in an XML message
Accessing databases from ESQL
Related reference
Syntax diagrams: available types
Complex ESQL functions