Creating and Customizing a Join in the Browser

Icon	Description
- Letter	Represents an alphanumeric data type.
- Number sign	Represents a numeric data type.
- Clock	Represents a date data type.

Procedure: How to Create a Join in the Browser

Expand the Reporting Object in which you want to create links between data sources.
The Reporting Object window opens.
Select the Join component and click Open.
The Join tool window opens and displays the data source associated with the Reporting Object.
In the Join toolbar, click Add Master.
The Add Master(s) dialog box opens.
Select a file and click OK.
WebFOCUS adds the field list for the selected data source to the Join tool window.
To link the data sources:
1. Click and hold a field name in one data source. This file becomes the host file.
2. Drag the field name to the field list of the other data source. This file becomes the cross-referenced file.
3. Position the cursor over the field name you want to link in the cross-referenced file and release the mouse button.
  The Join tool displays the connector line between the linked fields and the red and blue buttons next to the linked fields.
  
  To link fields other than those indicated by the Join tool, right-click the connector line and select Delete from the Link Properties dialog box. You can then follow the directions in Step 5 to link the fields you want.
Close the Join tool window to return to the Reporting Object window.

If you have finished working with the components of the Reporting Object, remember to click Done in the Reporting Object window to save the changes.

Note: Syntax and error checking are not performed on the commands you enter until the Reporting Object is executed.

Procedure: How to Create a Left Outer Join in the Browser

Open the Join tool.
Create your join.
In the Join tool window toolbar, click the Joins icon.
The Join Properties window opens.
Select your join properties:
- 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. For example, 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.
Click OK.

Reference: Usage Notes for Creating Joins

Vertical and horizontal scroll bars are provided as needed when the objects in the window exceed its size.
Validation of the join structure is accomplished when a join is created or a link is added to the join. Join code is sent to the WebFOCUS Server, but no data retrieval is performed. If the Join is valid, the join link is created within the Applet Join tool. If an error occurs on the WebFOCUS Reporting Server, a message is returned:
```
FOC214 MORE THAN 64 SEGMENTS OR 189 SEGMENTS + INDEXES DESCRIBED.
```
The MR Applet WHERE, DEFINE, and JOIN components do not support DEFINE-based joins. If you create a DEFINE-based join with the Developer Studio Join tool, you cannot open the MR Applet WHERE, DEFINE, and JOIN components.
A Reporting Object created in Developer Studio that includes a WHERE-based join cannot be edited in Managed Reporting Domain Builder, since a WHERE-based join is not supported in Reporting Objects edited in Managed Reporting Domain Builder. End user access of the Reporting Object is supported and works correctly.
The Applet Join tool generates a redundant join error message when you create a second recursive JOIN in the MR Applet interface. This error message occurs because the Applet Join tool does not have JOIN tag support and there is no way for users to identify a tag name as they can in Report Assistant and Developer Studio.

Automatically Repositioning Files in the Join Tool

There is an option in the Managed Reporting Join tool Applet that enables you to automatically arrange the files that currently display in the tool. This is useful when you add and remove files and there is extra space.

To reorder files, in the Applet Join toolbar, click the Auto Arrange Master Files Auto Arrange Master Files icon icon.

Customizing a Join in the Browser

How to:

You use the Join Properties dialog box to view the actual WebFOCUS code that defines a join, create single instance (unique) joins, rename the join, or delete the join.

The Join tool assigns a unique default name to each join you create in a Reporting Object. The default names are J1 for the first join, J2 for the second, J3 for the third, and so on. You use these names to access a Join Properties dialog box.

The following image shows the Join Properties dialog box containing the name of the created join and the WebFOCUS syntax for the join.

The Join Properties dialog box

Top of page

Procedure: How to Display Join Syntax in the Browser

In the Join tool window toolbar, click the Joins icon.
The Join Properties dialog box opens.
Select a join from the Created Joins list.
The WebFOCUS code that defines the join appears in the Focexec box. You cannot edit this code; you can only view it.

Top of page

Procedure: How to Create a Single Instance (Unique) Join in the Browser

In the Join tool window toolbar, click the Joins icon.
The Join Properties dialog box opens.
Select the Single Instance option button.
Click OK.

Top of page

Procedure: How to Rename a Join in the Browser

In the Join tool window toolbar, click the Joins icon.
The Join Properties dialog box opens.
Select a join from the Created Joins list.
In the adjacent text box, type a name for the join.
Click OK in the Join Properties dialog box.
Note: Alternatively, you can change the join name from the Link Properties dialog box. You can access this dialog box by right-clicking the arrow in the join connector line in the Join tool window.

Top of page

Procedure: How to Delete a Join in the Browser

Open the Join tool.
In the Join tool window, right-click the arrow in the join's connector line.
The Link Properties dialog box opens.
From the Link Properties dialog box, click the Delete Link button.
The Join tool deletes the link and you return to the Join tool window. Notice that the Join tool removed the join connector line and the red and blue buttons associated with the join.