Joining Different Kinds of Data Sources

In this section:

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.

You must consider the following when joining files:

Top of page

Data Source Requirements for Cross-Referenced Fields


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:

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

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


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:

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