This section describes how to configure the connector to extract data from multiple tables using a JOIN command in your SQL statement.
To extract data from multiple tables using a SQL JOIN
Open the Oracle Connector configuration file, and find the [FetchTasks]
section.
[FetchTasks] Number=1 0=ExampleDatabase [ExampleDatabase] ConnectionString=... SQL=select * from orders PrimaryKeys=OrderID Template=template.tmpl SubTables=Customers [Customers] Template=customers.tmpl SQL=select * from Customers where CustomerID=@CustomerID
Modify the SubTables
parameter so that it references a new section in the configuration file:
SubTables=Customers,ItemDetails
Create a new section in the configuration file, using the name you specified in the SubTables
parameter. In the new section, specify the following parameters:
Template
|
The path to a template file. The template file defines how the data is indexed into IDOL Server. |
SQL
|
A SQL statement to retrieve the information from the sub-tables. This example uses a JOIN command to extract data from both the Items and the Products tables. |
For example:
[ItemDetails] Template=items.tmpl SQL=SELECT * FROM Items INNER JOIN Products ON products.productID = items.productID WHERE orderID=@orderID
The connector runs this query for every row returned by the main query (select * from orders
). When the connector runs the query, it automatically replaces @orderID
with the OrderID retrieved from the main table (orders
).
|