SQL Tree Example
This section illustrates how to use SQL trees to retrieve data from an Oracle Applications system and to store it in a hierarchical format that is easy to access within flows.
Suppose you want a service to execute a transaction that retrieves customer names and phone numbers using a customer ID input parameter. For this example, the database contains the following tables and data.
TABLE1 looks like this:
ID | STATUS | NAME |
001 | N | John Smith |
TABLE2 looks like this:
TABLE3 looks like this:
ID | STREET |
001 | Main Street |
001 | Broadway Blvd. |
Also, suppose you want the output from the transaction to be a results document in the following format:
Based on the customer's status, you want to use the customer's ID to retrieve the rest of the customer's information. To retrieve the customer's phone number, you want to use an existing stored procedure: GetCustomerPhone.
To define the transaction definition you will create a parent alias and two child aliases in the SQL tree, as follows:
