可以使用与消息数据和数据库都进行交互的 SELECT 语句。 还可以嵌套 SELECT 语句,该语句与另一个 SELECT 语句中的一种数据类型进行交互,而该另一个 SELECT 语句又与其他类型的数据进行交互。
请参阅下列包含两个客户发票信息的输入消息:
<Data> <Invoice> <CustomerNumber>1234</CustomerNumber> <Item> <PartNumber>1</PartNumber> <Quantity>9876</Quantity> </Item> <Item> <PartNumber>2</PartNumber> <Quantity>8765</Quantity> </Item> </Invoice> <Invoice> <CustomerNumber>2345</CustomerNumber> <Item> <PartNumber>2</PartNumber> <Quantity>7654</Quantity> </Item> <Item> <PartNumber>1</PartNumber> <Quantity>6543</Quantity> </Item> </Invoice> </Data>
请参阅下列数据库表 Prices 和 Addresses 以及它们的内容:
PARTNO PRICE ----------- ------------------------ 1 +2.50000E+001 2 +6.50000E+00 PARTNO STREET CITY COUNTRY ------ ------------------- -------------- ------- 1234 22 Railway Cuttings East Cheam England 2345 The Warren Watership Down England
如果您对下列 ESQL 转换进行编码:
-- Create a valid output message SET OutputRoot.MQMD = InputRoot.MQMD; -- Select suitable invoices SET OutputRoot.XML.Data.Statement[] = (SELECT I.CustomerNumber AS Customer.Number, A.Street AS Customer.Street, A.City AS Customer.Town, A.Country AS Customer.Country, -- Select suitable items (SELECT II.PartNumber AS PartNumber, II.Quantity AS Quantity, PI.Price AS Price FROM Database.db2admin.Prices AS PI, I.Item[] AS II WHERE II.PartNumber = PI.PartNo ) AS Purchases.Item[] FROM Database.db2admin.Addresses AS A, InputRoot.XML.Data.Invoice[] AS I WHERE I.CustomerNumber = A.PartNo );
生成下列输出消息:输入消息已增加了从数据库表中获取的价格和地址信息:
<Data> <Statement> <Customer> <Number>1234</Number> <Street>22 Railway Cuttings</Street> <Town>East Cheam</Town> <Country>England</Country> </Customer> <Purchases> <Item> <PartNumber>1</PartNumber> <Quantity>9876</Quantity> <Price>2.5E+1</Price> </Item> <Item> <PartNumber>2</PartNumber> <Quantity>8765</Quantity> <Price>6.5E+1</Price> </Item> </Purchases> </Statement> <Statement> <Customer> <Number>2345</Number> <Street>The Warren</Street> <Town>Watership Down</Town> <Country>England</Country> </Customer> <Purchases> <Item> <PartNumber>1</PartNumber> <Quantity>6543</Quantity> <Price>2.5E+1</Price></Item> <Item> <PartNumber>2</PartNumber> <Quantity>7654</Quantity> <Price>6.5E+1</Price> </Item> </Purchases> </Statement> </Data>
可以在消息 SELECT 语句中嵌套数据库 SELECT。在多少情况下,这不然前一个示例有效,但是您可能发现如果消息小而数据库表大,这个比较好。
-- Create a valid output message SET OutputRoot.MQMD = InputRoot.MQMD; -- Select suitable invoices SET OutputRoot.XML.Data.Statement[] = (SELECT I.CustomerNumber AS Customer.Number, -- Look up the address THE ( SELECT A.Street, A.City AS Town, A.Country FROM Database.db2admin.Addresses AS A WHERE A.PartNo = I.CustomerNumber ) AS Customer, -- Select suitable items (SELECT II.PartNumber AS PartNumber, II.Quantity AS Quantity, -- Look up the price THE (SELECT ITEM P.Price FROM Database.db2admin.Prices AS P WHERE P.PartNo = II.PartNumber ) AS Price FROM I.Item[] AS II ) AS Purchases.Item[] FROM InputRoot.XML.Data.Invoice[] AS I );