Accessing multiple database tables

The following example assumes that you have two database tables called USERTABLE1 and USERTABLE2. Both tables have two char(6) data type columns (or equivalent).

USERTABLE1 contains two rows:

USERTABLE2 contains two rows:

All tables referenced by a single SELECT function must be in the same database. The database can be either the default (specified on the Data Source property of the node) or another database (specified on the FROM clause of the SELECT function).

Configure the Compute, Database, or Filter node that you are using to identify the database in which you have defined the tables. For example, if you are using the default database, right-click the node, select Open ESQL, and code the following ESQL statements in the module for this node:

SET OutputRoot.XML.Test.Result[] =
         (SELECT A.Column1 AS FirstColumn,
                 A.Column2 AS SecondColumn,
                 B.Column3 AS ThirdColumn,
                 B.Column4 AS FourthColumn
            FROM Database.USERTABLE1 AS A,
                 Database.USERTABLE2 AS B
           WHERE A.Column1 = 'value1' AND
                 B.Column4 = 'value8'
         ); 

This code results in the following output message content:

<Test>
  <Result>
    <FirstColumn>value1</FirstColumn>
    <SecondColumn>value2</SecondColumn>
    <ThirdColumn>value7</ThirdColumn>
    <FourthColumn>value8</FourthColumn>
  </Result>
</Test>

This example shows how to access data from two database tables. You can code more complex FROM clauses to access multiple database tables (although all the tables must be in the same database). You can also refer to one or more message trees, and can use SELECT to join tables with tables, messages with messages, or tables with messages. Joining data from messages and database tables provides an example of how to merge message data with data in a database table.

If you specify an ESQL function or procedure on the column identifier in the WHERE clause, it is processed as part of the database query and not as ESQL.

Consider the following example:

  SET OutputRoot.XML.Test.Result = 
     THE(SELECT ITEM T.Column1 FROM Database.USERTABLE1 AS T 
     WHERE UPPER(T.Column2) = 'VALUE2');

This code attempts to return the rows where the value of Column2 converted to uppercase is VALUE2. However, only the database manager can determine the value of T.Column2 for any given row, therefore it cannot be processed by ESQL before the database query is issued, because the WHERE clause determines the rows that are returned to the message flow.

Therefore, the UPPER is passed to the database manager to be included as part of its processing. However, if the database manager cannot process the token within the SELECT statement, an error is returned.