Integration Platform Technologies: Siebel Enterprise Application Integration > External Business Components >

About Joins to Tables in External Data Sources

Joins from business components, based on the default data source to a table in an external data source, are supported in the Siebel application.

Like other joined fields, the fields based on the join to the EBC are read-only.

The limitations for joining business components to tables in an external data source are as follows:

• The source field for the join must be based on a table in the default data source.
• The destination column of the join must be the column mapped to System Field Id.
• Multiple single join specifications are not supported for the join to the external table.
• Reverse navigation (for example, a call to go to the last record) is not supported when fields from multiple data sources are active.

Join Constraints are supported. Joins to more than one external table may be specified. However, increasing the number of external joined data sources can cause degradation in performance.

About Searching and Sorting on Fields Joined to External Tables

Fields based on a join to an external table can be searched and sorted. However, limitations do exist. The limitations for searching and sorting on fields joined to an external table follow:

• All fields in the sort specification must either be based on columns in the same external table, or be based on columns in the default data source.
• Named search specifications cannot be set on fields from an external data source.

Performance tests are recommended if searching and sorting are permitted on the fields based on joins to the external tables. The Siebel application does not have information of the data shape in the external tables. The Siebel application follows a rule-based approach to decide the order in which to query the external tables.

For example, consider the case where there are search and sort specifications on the fields in the Siebel Data Source but none on the fields from the external data source. The Siebel application decides to query the Siebel tables first. Only the rows matching the query specification in the current workset are retrieved from the external data source. As more rows are retrieved from the tables in the Siebel Data Source, the rows from the external data source are also retrieved.

The rules become complex when Search and Sort Specifications are applied to multiple data sources. The rules followed are based on the following requirements:

1. Retrieving the first few rows quickly
2. Shipping the least amount of data between the Siebel and external data sources
3. Eliminating a sort step

Step 2 and Step 3 may produce competing results. In that case, Step 2 takes precedence.

If, as result of the search and sort specifications in effect, the external table on which the Sort is based is not the driving table, the Siebel application raises an error if more than 1000 rows are retrieved. Refine the query specification in the event of this error.

Directives specified using the Business Component User property External DataSource Field Priority On Search to allow hinting of the order in which the tables in the data sources should be queried are supported. These directives may be applied based on a knowledge of the data shape in the Siebel and external tables.

For example, using the following property values:

A query on Field A is likely to be selective. If there is a search specification on Field A, the table that field A is based on is considered the driving table.

A query on Field B is likely to be selective. If there is a search specification on Field B and none on Field A, the table that field B is based on is considered the driving table.