Joins

In this section:

If a report requires data from two or more related data sources, you can temporarily join the files and report from them as if they were one. Joined files remain physically separate, but are treated as one data source structure throughout the session or until you clear the Join.

You can also create a new file structure for reporting by joining existing synonyms of relational tables using the Cluster Joins editor.


Top of page

x
What Is a Join?

A Join is a temporary connection between two or more data source files that share at least one common field. For example, since the EMPLOYEE data source includes a job code field and the JOBFILE data source also includes a job code field, you can join the two data sources.

The field format and the type must be the same for the common fields, but the field name need not be the same.

After you join two files, each time a record is retrieved from the first file (host file), it also retrieves the matching records from the second file (cross-referenced file).


Top of page

x
Types of Joins

In this section:

The following section describes the types of joins you can create using the Join functionality.



x
Dynamic Joins

Dynamic joins allow you to join two or more related data sources and create a virtual structure for reporting. The data sources remain physically separate, but App Studio treats them as a single structure. Dynamic joins include:

You can use dynamic joins for the duration of your session or until you clear the Join. Dynamic joins can read data from the joined data sources using the TABLE and GRAPH facilities.



x
Conditional Joins

Conditional joins use WHERE-based syntax to specify joins based on conditional criteria, not just on equality between fields. Additionally, the host and cross-referenced join fields do not have to contain matching formats.

Suppose you have a data source that lists employees by their ID number (the host file) and another data source that lists training courses and the employees who attended those courses (the cross-referenced file). Using a conditional join, you can join employee ID in the host file to employee ID in the cross-referenced file to determine which employees took training courses in a given date range (the WHERE condition).



x
Single Instance and Multiple Instance Joins

The Join functionality allows you to create single instance and multiple instance joins. A single instance, or one-to-one join structure matches one value in the host data source to one value in the cross-referenced data source. Joining an employee ID in an employee data source to an employee ID in a salary data source is an example of a single instance join.

A multiple instance or one-to-many join structure matches one value in the host data source to multiple values in the cross-referenced field. Joining employee ID in the employee data source of a company to employee ID in a data source that lists all the training classes offered by that company would result in a listing of all courses taken by each employee, or a joining of the one instance of each ID in the host file to the multiple instances of that ID in the cross-referenced file.

When you create a dynamic or conditional join using the Join functionality, you can specify whether the join is a single instance or a multiple instance.





x
Joins Based on Virtual Fields

Joins that are based on virtual fields use DEFINE-based syntax to create a virtual field in the host file that you can then join to a real cross-referenced field. The DEFINE expression that creates the virtual host field may contain only fields in the host file and constants. It may not contain fields in the cross-referenced file.

You can join a virtual field to more than one real field in more than one cross-referenced file using separate Join commands, each referring to the same host file.



x
Left Outer Joins

When a report omits host rows that lack corresponding cross-referenced rows, the join is called an inner join. When a report displays all matching rows plus all rows from the host file that lack corresponding cross-referenced rows, the join is called a left outer join.

For details on left outer join syntax, see the Creating Reports With WebFOCUS Language manual.

Note: If the SET ALL=ON or SET ALL=PASS setting is applied, it impacts all joins in the procedure and they are treated as left outer joins.



x
Host Records With No Matching Cross-Referenced Records

Sometimes there is no matching record in the cross-referenced file. A host record that has no matching cross-referenced record is called a short path.

The report that results when a host record lacks a corresponding cross-referenced record depends on the following factors:


WebFOCUS