SELECT function

SELECT is a complex function that combines, filters, and reformats complex message and database data.

Syntax

  • Source is a reference to a field, field array, or database table
  • 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.

Related concepts
ESQL

Related tasks
Developing ESQL
Transforming a simple XML message
Transforming a complex XML message
Returning a scalar value in an XML message
Interacting with user databases

Related reference
Syntax preference
Complex ESQL functions