WebFOCUS Online Help > Managed Reporting Developer > Creating Reporting Objects > Creating a Join in Developer Studio
The Join Editor provides a graphical method for creating and manipulating Joins. You must specify a host file, then a cross-referenced file to create a join.
You create the Join as an object, separate from any other object or procedure. You are responsible for placing the Join object in the correct position within your procedure (that is, before a Define, Report, or Graph), and for running it.
Since every procedure creates a new session on the Reporting Server when it is run, the duration of your connection is always limited to a single procedure. Therefore, any Join issued at the beginning of a procedure will be in effect only for those report requests that are called in the same procedure.
You can open the Join Editor from either the toolbar or from a reporting tool. Before the tool opens you will be asked to select a host file.
The Join Editor displays both files and, ordinarily, a default Join.
The following image shows the Join Editor containing the EMPLOYEE and JOBFILE files.
The following items provide additional information to help you better understand the process of creating a join in Developer Studio:
icon in front of the field, and
virtual fields have a define 
icon in front of the field. A
virtual field is a temporary field that does not actually exist
in the data source; its value is computed based on an expression
you write. After it is created, the virtual field is treated as
if it were a field in the data source. For more information, see
the WebFOCUS Managed Reporting End User's Manual.
The following image shows the Join Editor toolbar which contains a series of buttons to use when editing joins.
When you access the Join Editor, a toolbar opens below the title bar. You can access the following buttons from the toolbar as described in the table below:
|
Button |
Description |
|---|---|
|
|
Creates additional concurrent joins. See Customizing a Join in Developer Studio for more information. |
|
|
Selects a cross-referenced file to join to the host file you have already selected. See How to Create Additional Joins in Developer Studio for more information. |
|
|
Launches the Join Clear window. |
|
|
Runs a join. |
|
|
Deletes a selected object (table or join). |
|
|
Launches the Join Define in File dialog box to create a virtual field in the host file. |
|
|
Launches the WHERE Expression Builder to create WHERE criteria and change the join type to a conditional join. |
|
|
Shows or hides the WebFOCUS code generated by the Join Editor. |
|
|
Automatically links the data sources that are added between fields that have a common name and format. For FOCUS files, the fields must also be indexed. This option is enabled by default. |
|
|
Creates a cascade view of multiple joins (default). |
|
|
Splits the Join Editor horizontally when working with multiple joins. |
|
|
Splits the Join Editor vertically when working with multiple joins. |
The WebFOCUS Table List dialog box opens.
The Join Editor opens showing a Fields window for the host file that you selected.
The WebFOCUS Table List opens again.
A default Join is created if the host and cross-referenced files share appropriate fields. If a default Join is not created, follow the instructions in Customizing a Join in Developer Studio to create your own.
Note that there are several different ways to create a Left Outer Join using the Join Tool in Developer Studio.
The Join Properties dialog box opens as shown in the following image:
Type a tag name for the data source.
Type a tag name of the cross-referenced file.
Displays the field that has been joined from the host file to the cross-referenced file. A single arrow indicates a single instance (unique) Join; a double arrow indicates a multiple instance (non-unique) Join.
Displays the name of the join. To change the default join name, type a new name in this field and click OK.
Note: The join name must be unique and cannot exceed eight characters.
Type a description. This is not used in the Join command. It is used for reference purposes. A comment (-*) is added to the procedure.
Indicates the type of join. Choose from the following:
Multiple Instance (formerly Non Unique). A one-to-many join structure that matches one value in the host data source to multiple values in the cross-referenced field. Joining employee ID in a company employee data source 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.
Single Instance (formerly Unique). A one-to-one join structure that 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 (unique) join.
Unspecified. This option is selected in the Join properties when a join that was created in a release earlier than Version 7 Release 1 is opened in the Join Tool. In versions prior to Version 7 Release 1, the defaults for join type and other operators were assumed and not specified in the generated code. Since the Join Tool cannot determine the type of join that was created and if there were other commands set to control the join, select the appropriate options (Inner Join, Left Outer Join, Multiple Instances, Single Instances) from the Join Tool to upgrade the generated code.
Inner Join. A join that results when a report omits host rows that lack corresponding cross-referenced rows.
Left Outer Join. Extends the results of an Inner Join and retrieves records from both host and cross-referenced tables, including all records from the left table (host) and any records from the right table (cross-referenced) where the condition values match. If there are no matching values in the cross-referenced table, the join still retrieves records from the host table.
The Field list for each data source being joined displays the field names by default. To help you build meaningful joins you can add information about the listed fields.
The selected information categories appear as headings in the field list for each joined file. Details will appear if the corresponding information is included in the Master File.
When you exit the Join window, you are prompted to save your work. If you save the Join, it is added to the procedure in which it has been created. See How to Create Additional Joins in Developer Studio.
The Join Editor may automatically rearrange your saved Joins to display Joins that use some of the same files in one pane of the Join window. The following two Joins share a host file.
The following image shows the Join Editor window containing two separate join panes, one pane shows one join from EMPLOYEE and JOBFILE, and the other pane shows a join from EMPLOYEE and JOBHIST.
Tip: If the field names you wish to join are different, as in the join for EMP_ID and PIN in the right pane above, you must drag and drop EMP_ID or PIN to make the connection.
Note: If you close the Join window and then reopen it, the two Joins appear in the same pane.
The following image shows the Join Editor window containing one join pane showing EMPLOYEE linking to JOBFILE and JOBHIST.
| WebFOCUS |
- New Join
- Add File
- Clear Join
- Run Join
- Delete
- Define Field
- Create/Edit Selection
- Show/Hide Source Code
- Toggle Auto Join
- Cascade
- Horizontal Tile
- Vertical Tile
