Joining Different Kinds of Data Sources

In addition to joining FOCUS data sources, you can join other kinds of data sources. For example, you can join a FOCUS data source to a DB2 table. The fact that the data sources are from different systems is transparent to you as long as you have the appropriate data adapter installed for each non-FOCUS data source you want to access.

Data Source Requirements for Cross-Referenced Fields

Reference:

Joining Cross-Referenced Fields in Specific Types of Data Sources

Developer Studio supports a wide variety of data sources. In most cases, you can join any data source to any other data source as long they share at least one common field. This is sometimes called a universal Join.

In general, the host field in a Join can be any field. However, some fields may not be valid as cross-referenced fields.

Certain data sources impose additional restrictions on cross-referenced fields. For example:

Some data sources require the cross-referenced field to have an index. An index in a data source is similar to an index in a book. Just as you look up a topic in an index to find the correct page without reading the entire book, the database system looks up a field value in its index so it can go to the correct location without reading the entire data source.
Some data sources require the cross-referenced field to be a key. A key is a field (or group of fields) that distinguishes one record from another. In the EMPLOYEE data source, the employee ID is the key. Its value identifies a particular employee record.

For details, see Joining Cross-Referenced Fields in Specific Types of Data Sources.

Top of page

Reference: Joining Cross-Referenced Fields in Specific Types of Data Sources

The following table describes conditions that apply when you join cross-referenced fields in specific types of data sources.

Type of File	Conditions for Join
FOCUS	The cross-referenced field must be indexed.
Relational (DB2, Oracle, Microsoft SQL, Sybase Server, Informix, MS SQL OLAP Services, Microsoft Access, Hyperion ESSBase, ODBC, EDA)	Any field can be the cross-referenced field.
Fixed sequential	Any field can be the cross-referenced field, but it must be sorted the same way as the host field. If the cross-referenced file contains only one segment, the host file must have a segment declaration.

Joining a Data Source to Itself: The Recursive Join

There may be situations in which you need to join a data source to itself. As an example, consider a data source that lists parts for an airplane. Some parts, such as cabins, are made up of smaller parts, such as seats. Therefore, this data source has:

A root segment with an instance for every part, including its description.
A descendant segment connecting a part to each of its subparts.

Certain subparts may themselves be composed of smaller parts. If you join the subpart segment to the root segment, you can go from a subpart record to its description record in the root segment to its component part records, as shown in the following diagram.

recursive join