Ao codificar o ESQL para um nó Compute, utilize a função SELECT para transformação de mensagem complexa.
Neste exemplo, Fatura contém um número variável de Itens. A transformação é mostrada abaixo:
SET OutputRoot.XMLNS.Data.Statement[] = (SELECT I.Customer.Title AS Customer.Title, I.Customer.FirstName || ' ' || I.Customer.LastName AS Customer.Name, COALESCE(I.Customer.PhoneHome,'') AS Customer.Phone, (SELECT II.Title AS Desc, CAST(II.UnitPrice AS FLOAT) * 1.6 AS Cost, II.Quantity AS Qty FROM I.Purchases.Item[] AS II WHERE II.UnitPrice > 0.0 ) AS Purchases.Article[], (SELECT SUM( CAST(II.UnitPrice AS FLOAT) * CAST(II.Quantity AS FLOAT) * 1.6 ) FROM I.Purchases.Item[] AS II ) AS Amount, 'Dollars' AS Amount.(XML.Attribute)Currency FROM InputRoot.XMLNS.Invoice[] AS I WHERE I.Customer.LastName <> 'Brown' );
A mensagem de saída gerada é:
<Dados> <Statement> <Customer> <Title>Mr</Title> <Name>Andrew Smith</Name> <Phone>01962818000</Phone> </Customer> <Purchases> <Article> <Desc Category="Computer" Form="Paperback" Edition="2">The XML Companion</Desc> <Cost>4.472E+1</Cost> <Qty>2</Qty> </Article> <Article> <Desc Category="Computer" Form="Paperback" Edition="2"> A Complete Guide to DB2 Universal Database</Desc> <Cost>6.872E+1</Cost> <Qty>1</Qty> </Article> <Article> <Desc Category="Computer" Form="Hardcover" Edition="0">JAVA 2 Developers Handbook</Desc> <Cost>9.5984E+1</Cost> <Qty>1</Qty> </Article> </Purchases> <Amount Currency="Dollars">2.54144E+2</Amount> </Statement> </Data>
Essa transformação possui cláusulas SELECT aninhadas. A instrução externa opera na lista Invoices. A instrução interna opera na lista Items. A cláusula AS associada à cláusula SELECT interna espera uma matriz:
(SELECT II.Title AS Desc, CAST(II.UnitPrice AS FLOAT) * 1.6 AS Cost, II.Quantity AS Qty FROM I.Purchases.Item[] AS II WHERE II.UnitPrice > 0.0 ) -- Note the use of [] in the next expression AS Purchases.Article[],
Esta instrução informa à cláusula SELECT externa para esperar um número variável de itens em cada resultado. Cada cláusula SELECT possui seu próprio nome de correlação: I para a cláusula SELECT externa e II para a interna. Cada cláusula SELECT geralmente utiliza seu próprio nome de correlação, mas a cláusula FROM na cláusula SELECT interna refere-se ao nome de correlação da cláusula SELECT externa:
(SELECT II.Title AS Desc, CAST(II.UnitPrice AS FLOAT) * 1.6 AS Cost, II.Quantity AS Qty -- Note the use of I.Purchases.Item in the next expression FROM I.Purchases.Item[] AS II WHERE II.UnitPrice > 0.0 ) AS Purchases.Article[],
Esta instrução informa à cláusula SELECT interna para trabalhar com os Items da Invoice atual. Ambas cláusulas SELECT contêm cláusulas WHERE. A externa utiliza um critério para descartar determinados Clientes e a interna utiliza um critério diferente para descartar certos Itens. O exemplo também mostra a utilização de COALESCE para evitar que campos de entrada ausentes façam com que o campo de saída correspondente fique ausente. Por último, também utiliza a função de coluna SUM para incluir o valor de todos os Itens em cada Fatura. As funções de coluna são discutidas em Referenciando as Colunas em um Banco de Dados.
Quando os campos Desc são criados, todo o campo Título de entrada é copiado: ou seja, os atributos XML e o valor do campo. Se você não deseja esses atributos na mensagem de saída, utilize a função FIELDVALUE para descartá-los; por exemplo, codifique o seguinte ESQL:
SET OutputRoot.XMLNS.Data.Statement[] = (SELECT I.Customer.Title AS Customer.Title, I.Customer.FirstName || ' ' || I.Customer.LastName AS Customer.Name, COALESCE(I.Customer.PhoneHome,'') AS Customer.Phone, (SELECT FIELDVALUE(II.Title) AS Desc, CAST(II.UnitPrice AS FLOAT) * 1.6 AS Cost, II.Quantity AS Qty FROM I.Purchases.Item[] AS II WHERE II.UnitPrice > 0.0 ) AS Purchases.Article[], (SELECT SUM( CAST(II.UnitPrice AS FLOAT) * CAST(II.Quantity AS FLOAT) * 1.6 ) FROM I.Purchases.Item[] AS II ) AS Amount, 'Dollars' AS Amount.(XML.Attribute)Currency FROM InputRoot.XMLNS.Invoice[] AS I WHERE I.Customer.LastName <> 'Brown' );
O código gera a seguinte mensagem de saída:
<Dados> <Statement> <Customer> <Title>Mr</Title> <Name>Andrew Smith</Name> <Phone>01962818000</Phone> </Customer> <Purchases> <Article> <Desc>The XML Companion</Desc> <Cost>4.472E+1</Cost> <Qty>2</Qty> </Article> <Article> <Desc>A Complete Guide to DB2 Universal Database</Desc> <Cost>6.872E+1</Cost> <Qty>1</Qty> </Article> <Article> <Desc>JAVA 2 Developers Handbook</Desc> <Cost>9.5984E+1</Cost> <Qty>1</Qty> </Article> </Purchases> <Amount Currency="Dollars">2.54144E+2</Amount> </Statement> </Data>