Join Options Tab

Reference:

The Join options tab is used to create joins for a report. The maximum number of joins that can be optimized by an SQL adapter has been increased to 64, or to the maximum number permitted by each RDBMS, up to the 64 join limit.


Top of page

x
Reference: Join Options Tab: Add or New

The following image shows the Join options tab where you create joins for a report.

x

Select an existing join

Use the list box to select the join with which you want to work.

x
New

Opens the Masters List dialog box where you select the target data source.

Delete

Click to delete the join.

OK button

Click to select a Master File.

Cancel button

Click to return to the Add/New Join window.

x

When you select a target data source you have multiple join related settings and options, as shown in the following image.

The Join Settings section provides the following fields and buttons for creating joins:

Description

Enter a description for your join.

Internal name

WebFOCUS assigns an alphanumeric name for the join, for example J001.

x
Save & Create

Once you have selected your host and target fields, click Save & Create to create the join.

x
View

Click to view the syntax of your join, which displays the code sent to the WebFOCUS Reporting Server to validate the join, but may not be the same code required to run the report. This is the case for a DEFINE based JOIN where only the JOIN statement is displayed for validation, but both the JOIN and DEFINE statements are needed when the report is run.

xThe Join Type drop-down list provides the following join type options:

Inner Join

A join that results when a report omits host rows that lack corresponding cross-referenced rows.

Unspecified

This option is selected in the Join properties when a join that was created in an earlier release is opened. In prior versions, the defaults for join type and other operators were assumed and not specified in the generated code. Since the Report Assistant 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) to upgrade the generated code.

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.

xThe Instances drop-down list provides the following join instance options:

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 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.

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 unique join.

x xThe Host Field List contains:

Search (binoculars) icon

Opens the Field List Searching dialog box. See Searching Field Lists for details.

x
Define icon

Click the Define icon to select: New define field, Edit define field, or Delete define field. If you select the New define field or Edit define field, the Define Field Creator dialog box opens where you can create or edit a defined field.

Tree icon

Displays a tree structure of field names by segment with segment, alias, title, description, and format information below the tree. Individual field information appears when you click the field.

x
List icon

Displays a list of all field names with information that you select from the adjacent arrow that opens a list box: name, alias, title, format, description, segment, and file name. You can also choose to sort by file order.

Available fields

Lists of fields from the host data source for creating a join.

The Target Field List contains: x

Select a target button

Click to select a target Master File.

x x
Tree icon

Displays a tree structure of field names by segment with segment, alias, title, description, and format information below the tree. Individual field information appears when you click the field.

x
List icon

Displays a list of all field names with information that you select from the adjacent arrow that opens a list box: name, alias, title, format, description, segment, and file name. You can also choose to sort by file order.

Fields

Lists of fields from the target data source for creating a join.

x xFor both the Host and Target field lists, each has its own Selected Fields window that has:

Selected Fields window

Shows the fields selected from the data source for a join.

x
Add icon

Adds a field.

x
Remove icon

Removes a field.

x x
Move Up or Move Down icons

Moves the position of the field within the join.


WebFOCUS