Searching and Sorting on Fields Joined to External Tables
Fields based on a join to an external table, as described in About Joins to Tables in External Data Sources, 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.
For information on named search specifications, see the topic on the SetNamedSearch method in Siebel Object Interfaces Reference.
Performance tests are recommended if searching and sorting are permitted on fields based on joins to the external tables. The Siebel application does not have information on 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:
Retrieving the first few rows quickly
Shipping the least amount of data between the Siebel and external data sources
Eliminating a sort step
Step 2 and Step 3 might produce competing results. In that case, Step 2 takes precedence.
If, as result of the search and sort specifications in effect, then 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 will be queried are supported. These directives can be applied based on a knowledge of the data shape in the Siebel and external tables.
For example, using the following property values:
| Property | Value |
|---|---|
External DataSource Field Priority On Search: FieldA |
1 |
External DataSource Field Priority On Search: FieldB |
2 |
A query on Field A is likely to be selective. If there is a search specification on Field A, then 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, then the table that field B is based on is considered the driving table.