Recursive Relationships

Generally, you use one-to-one and one-to-many relationships to join two different segments, usually in two different data sources. However, you can also join the same data source, or even the same segment, to itself. This technique is called a recursive join.

See the Creating Reports With TIBCO WebFOCUS® Language manual for more information on recursive joins.

Example: A Recursive Join With a Single Segment

Assume that you have a single-segment data source called MANAGER, which includes the ID number of an employee, the employee name, and the ID number of the manager of the employee, as shown in the following image.

If you want to generate a report showing every employee ID number and name, and every manager ID number and name, you must join the segment to itself. Issue the following command:

JOIN MANAGER_ID IN MANAGER TO ID IN MANAGER AS BOSS

This creates the following structure:

Note: You can refer to fields uniquely in cross-referenced recursive segments by prefixing them with the first four letters of the join name (BOSS, in this example). The only exception is the cross-referenced field, for which the alias is prefixed instead of the field name.

After you have issued the join, you can generate an answer set that looks like this:

ID      NAME               MANAGER_ID     BOSSNAME
--      ----               ----------     --------
026255  JONES              837172         CRUZ
308743  MILBERG            619426         WINOKUR
846721  YUTANG             294857         CAPRISTI
743891  LUSTIG             089413         SMITH
585693  CAPRA              842918         JOHNSON

Example: A Recursive Join With Multiple Segments

You can join larger structures recursively as well. For example, consider a two-segment data source called AIRCRAFT that stores a bill-of-materials for an aircraft company. The root segment has the name and description of a part, and the child segment has the name of a subpart. For each part, there can be many subparts. This type of joined structure is illustrated in the following diagram.

While many of the larger parts are constructed of several levels of subparts, some of these subparts, such as bolts, are used throughout aircraft at many different levels. It is redundant to give each occurrence of a subpart its own segment instance. Instead, use the two-segment design shown previously and then join the data source to itself:

JOIN SUBPART IN AIRCRAFT TO PART IN AIRCRAFT AS SUB_PART

This produces the following data structure.