WebSphere Message Brokers
File: ak05830_
Writer: Bill Oppenheimer

Task topic

This build: July 31, 2007 21:32:32

Accessing multiple database tables

You can refer to multiple tables that you have created in the same database. Use the FROM clause on the SELECT statement to join the data from the two 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:

  Column1 Column2
Row 1 value1 value2
Row 2 value3 value4

USERTABLE2 contains two rows:

  Column3 Column4
Row 1 value5 value6
Row 2 value7 value8

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, Filter, or Database node that you’re using to identify the database in which you have defined the tables. For example, if you’re 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 results in the following output message content:

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

The example above 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 XML messages and database tables provides an example of how to merge message data with data in a database table.

(defined by the data source property of the node).

If you specify an ESQL function or procedure on the column identifier in the WHERE clause, this 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 attempts to return the rows where the value of Column2 converted to upper case is VALUE2. However, only the database manager can determine the value of T.Column2 for any given row, and 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.

Related concepts
Message flows overview
ESQL overview
Message modeling
Related tasks
Designing a message flow
Defining message flow content
Managing ESQL files
Joining data from XML messages and database tables
Related reference
Compute node
Database node
Filter node
ESQL reference
SELECT function
SET statement
Notices | Trademarks | Downloads | Library | Support | Feedback

Copyright IBM Corporation 1999, 2007Copyright IBM Corporation 1999, 2007. All Rights Reserved.
This build: July 31, 2007 21:32:32

ak05830_ This topic's URL is: