Returning a scalar value in a message

The following example shows the use of the ITEM keyword to select one item and create a single value.

SET OutputRoot.MQMD = InputRoot.MQMD;

SET OutputRoot.XMLNS.Test.Result[] = 
   (SELECT ITEM T.UnitPrice FROM InputBody.Invoice.Purchases.Item[] AS T);

When the Invoice message is received as input, the ESQL shown generates the following output message:

<Test>
  <Result>27.95</Result>
  <Result>42.95</Result>
  <Result>59.99</Result>
</Test>

When the ITEM keyword is specified, the output message includes a list of scalar values. Compare this message to the one that is produced if the ITEM keyword is omitted, in which a list of fields (name-value pairs) is generated:

<Test>
  <Result>
    <UnitPrice>27.95</UnitPrice>
  </Result>
  <Result>
    <UnitPrice>42.95</UnitPrice>
  </Result>
  <Result>
    <UnitPrice>59.99</UnitPrice>
  </Result>
</Test>

The THE keyword converts a list containing one item to the item itself.

The two previous examples both specified a list as the source of the SELECT in the FROM clause (the field reference has [] at the end to indicate an array), so typically the SELECT function generates a list of results. Because of this behavior, you must specify a list as the target of the assignment (thus the "Result[]" as the target of the assignment). However, you often know that the WHERE clause that you specify as part of the SELECT returns TRUE for only one item in the list. In this case use the THE keyword.

The following example shows the effect of using the THE keyword:

SET OutputRoot.MQMD = InputRoot.MQMD;

SET OutputRoot.XMLNS.Test.Result =              
    THE (SELECT T.Publisher, T.Author FROM InputBody.Invoice.Purchases.Item[] 
         AS T WHERE T.UnitPrice = 42.95);

The THE keyword means that the target of the assignment becomes OutputRoot.XMLNS.Test.Result (the "[]" is not permitted). Its use generates the following output message:

<Test>
  <Result>
    <Publisher>Morgan Kaufmann Publishers</Publisher>
    <Author>Don Chamberlin</Author>
  </Result>
</Test>

Consider the following sample input message:

<Test>
 <A>1</A>
 <A>2</A>
 <A>3</A>
 <A>4</A>
 <A>5</A>
</Test>

If you code the following ESQL statements to process this message:

SET OutputRoot.XMLNS.Test.A[] = 
  (SELECT ITEM A from InputBody.Test.A[]  
   WHERE CAST(A AS INTEGER) BETWEEN 2 AND 4);

the following output message is generated:

      <A>2</A>
      <A>3</A>
      <A>4</A>