This site contains the documentation that is relevant to older WSO2 product versions and offerings.
For the latest WSO2 documentation, visit https://wso2.com/documentation/.

Nested Query Sample

Owned by Former user (Deleted)
Last updated: Nov 23, 2017
2 min read

Note that WSO2 EI is shipped with the following changes to what is mentioned in this documentation:

  • <PRODUCT_HOME>/repository/samples/ directory that includes all Data Integration samples is changed to <EI_HOME>/samples/data-services/.
  • <PRODUCT_HOME>/repository/samples/resources/ directory that includes all artifacts related to the Data Integration samples is changed to <EI_HOME>/samples/data-services/resources/.

In a data service, the result of one query can sometimes be used as an input parameter for another query. In such situations, we use the nested query feature available in WSO2 Enterprise Integrator (WSO2 EI).

About the sample

The sample data service (NestedQuerySample) contains the following operations:

  • employeesInOffice: The employeesInOfficeSQL query takes in an office code and prints the details of all the employees in that office.
  • listOffices: The listOfficesSQL query lists all the offices in the database. When office information is printed, all the employees that belong to an office are also printed using a nested query, which refers to the employeesInOfficeSQL query (invoked by the "employeesInOffice" operation).
  • customerNameSQL: The customerNameSQL query takes in the customer number as a parameter and returns the customer name. In the result section of this query, there is no "rowName" defined (only an element wrapper). With this option, a separate element will not be created for every record in the result. However, the record content will be directly added to the outer element. This is best used in a query where there is only one record to be returned.
  • customerOrders: The customerOrdersSQL query prints the details of all the orders that are in the database. In the result elements, a nested query that refers to the customerNameSQL query is used. In this way, the name of the customer who placed the order can also be returned with the order details.

See Data integration for a definition of data services and operations.

Building the sample

The sample data service named NestedQuerySample should be deployed using the instructions in Samples Setup.

Running the sample

The sample service can be run using the TryIt tool, which is bundled with WSO2 EI, or a code-generated java client sample as discussed in the Data Services Clients section.

Follow the steps below to demonstrate this functionality using the TryIt tool:

  1. Log in to the management console of your server and click List under Services in the navigator. The NestedQuerySample will be listed here.
  2. Click Try this service to open the TryIt tool.
  3. Select an operation and click Send to execute the operation. You will get the product list in the response as shown below.
    • Operation invoking the listOfficesSQL query:
       
    • Operation invoking the customerOrdersSQL query:
       
    • Operation invoking the customerNameSQL query:

      Enter "103" as the customer name and send the request as shown below.
       
    • Operation invoking the employeesInOfficeSQL query:

      First, see the result of "listOffices" operation to get the office code. Then, enter the office code ("1") and send the request as shown below. 
      Â