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.
