Referencing columns in a database

While the standard SQL SELECT syntax is supported for queries to an external database, there are a number of points to be borne in mind. You must prefix the name of the table with the keyword Database to indicate that the SELECT is to be targeted at the external database, rather than at a repeating structure in the message.

The basic form of database SELECT is:

SELECT ...
  FROM Database.TABLE1
  WHERE ...

If necessary, you can specify a schema name:

SELECT ...
  FROM Database.SCHEMA.TABLE1
  WHERE ...

where SCHEMA is the name of the schema in which the table TABLE1 is defined. Include the schema if the user ID under which you are running does not match the schema. For example, if your userID is USER1, the expression Database.TABLE1 is equivalent to Database.USER1.TABLE1. However, if the schema associated with the table in the database is db2admin, you must specify Database.db2admin.TABLE1. If you do not include the schema, and this does not match your current user ID, the integration node generates a runtime error when a message is processed by the message flow.

SELECT ...
  FROM Database.DataSource.SCHEMA.TABLE1
  WHERE ...

Qualify references to column names with either the table name or the correlation name defined for the table by the FROM clause. So, where you could normally execute a query such as:

SELECT column1, column2 FROM table1

you must write one of the following two forms:

SELECT T.column1, T.column2 FROM Database.table1 AS T
 
SELECT table1.column1, table1.column2 FROM Database.table1

This is necessary in order to distinguish references to database columns from any references to fields in a message that might also appear in the SELECT:

SELECT T.column1, T.column2 FROM Database.table1
  AS T WHERE T.column3 = Body.Field2

SELECT T.column1 AS price, T.column2 AS item 
  FROM Database.table1 AS T WHERE...

The standard select all SQL option is supported in the SELECT clause. If you use this option, you must qualify the column names with either the table name or the correlation name defined for the table. For example:

SELECT T.* FROM Database.Table1 AS T

When you use ESQL procedure and function names within a database query, the positioning of these within the call affects how these names are processed. If it is determined that the procedure or function affects the results returned by the query, it is not processed as ESQL and is passed as part of the database call.

This applies when attempting to use a function or procedure name with the column identifiers within the SELECT statement.

For example, if you use a CAST statement on a column identifier specified in the Select clause, this is used during the database query to determine the data type of the data being returned for that column. An ESQL CAST is not performed to that ESQL data type, and the data returned is affected by the database interaction's interpretation of that data type.

If you use a function or procedure on a column identifier specified in the WHERE clause, this is passed directly to the database manager for processing.

The examples in the subsequent topics illustrate how the results sets of external database queries are represented in IBM® Integration Bus. The results of database queries are assigned to fields in a message using a Compute node.

A column function is a function that takes the values of a single column in all the selected rows of a table or message and returns a single scalar result.