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 |
Configure the Compute node to identify the database in which you have defined both tables. 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 tables must be from a single database (defined by the Data Source property of the node). 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.
If you specify an ESQL function or procedure name is specified 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.
Notices |
Trademarks |
Downloads |
Library |
Support |
Feedback
![]() ![]() |
ak05830_ |