Creating and Customizing a Join in Developer Studio

In this section:

A join is a temporary connection between two or more data sources that share at least one common field. After you join two data sources, each time WebFOCUS retrieves a record from the first data source (host file), it also retrieves the matching records from the second data source (cross-referenced file). For a full description of joining data sources using the browser, see the Creating Reports With WebFOCUS Language manual. For a full description of joining data sources using Developer Studio, see the Creating Reporting Applications With Developer Studio manual.

You use the Join tool to link data sources. The Join tool provides a graphical method for creating and manipulating joins. Instead of coding a join using the WebFOCUS language, you use the Join tool to drag a field from one data source and link it to a field in another data source.

When you access the Join tool, the window displays a field list for the data source associated with the Reporting Object (which you specified when you created the Reporting Object) and a field list for any data source you add to the window. An icon representing the data type appears before each field name in the Master File.

Note: When joining two FOCUS data sources, the cross-referenced file field must be indexed.

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.

Top of page
x
Creating a Join With Graphical Tools

How to:

Reference:

The Join tool 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. The Join remains active for the entire session, unless you explicitly clear it.

You can open the Join tool from either the toolbar or from a reporting tool. Before the tool opens you will be asked to select a host file.

The Join tool displays both files and, ordinarily, a default Join:

Join tool

x
Reference: Join Toolbar

When you access the Join tool a toolbar opens below the title bar. You can access the following commands from the toolbar:

Button

Command

Description

Save

Save

Saves the join.

New Join

New Join

Creates additional concurrent joins.

Note: You may also select New Join from the right-click context menu in the Join window.

Add File

Add File

Selects a cross-referenced file to join to the host file you have already selected.

Note: You may also select Add File from the right-click context menu in the Join window.

Clear Join

Clear Join

Launches the Join Clear window.

Note: You may also select Clear Join from the right-click context menu in the Join window.

Run Join

Run Join

Runs a join to check syntax.

Note: You may also select Run from the right-click context menu in the Join window.

Delete

Delete

Deletes a selected object (table or join).

Define Field

Define Field

Launches the Define dialog to create a virtual field in the host file.

Create/Edit Selection

Create/Edit Selection

Launches the WHERE Expression Builder to create WHERE criteria and change the join type to a conditional join. This is enabled only when the host table is selected.

Show/Hide Source Code

Show/Hide Source Code

Shows or hides the FOCUS code generated by the Join tool. This is enabled only when the Join link is selected.

Toggle Auto Join

Toggle Auto Join

Performs automatic joins within tables.

Cascade

Cascade

Creates a cascade view of multiple joins (default).

Horizontal Tile

Horizontal Tile

Splits the Join tool horizontally when working with multiple joins.

Vertical Tile

Vertical Tile

Splits the Join tool vertically when working with multiple joins.
x
Procedure: How to Create a Join

If you want to define a Join in a procedure:

  1. Right-click the procedure in the Procedures folder and choose Edit in Developer Studio Tool from the context menu.

    The Procedure Viewer opens.

  2. Click the component connector (yellow diamond) at the point where you want to include the join in the procedure, then click the Join button on the component connector toolbar.
  3. Choose a Master File for the data source you wish to use as your host file and click Open.

    The Join tool opens showing a Fields window for the host file that you selected.

  4. To add a cross-referenced data source to the Join, click the Add File button on the Join toolbar or choose File from the Developer Studio Insert menu.

    Tip: You may also right-click anywhere in the Join window and select Add File from the context menu.

  5. Select a cross-referenced file.
  6. Click OK.

    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 to create your own.
x
Procedure: How to Create a Join for a Reporting Object in Developer Studio

Note: This procedure applies to creating a Join in the Managed Reporting environment.

  1. In the Reporting Object window, double-click Join.

    The WebFOCUS Table List dialog box opens.

  2. Choose a Master File for the data source you wish to use as your host file, and click OK.

    The Join tool opens showing a Fields window for the host file that you selected.

  3. To add a cross-referenced data source to the Join, click the Add File button.

    The WebFOCUS Table List opens again.

  4. Select a cross-referenced file.
  5. Click OK.

    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 to create your own.
x
Procedure: How to Create a Left Outer Join With the Join Tool in Developer Studio
  1. To open the Join Properties dialog box, do one of the following:
    • In the Join tool, right-click a join link and select Left Outer Join.

      Left Outer Join

      or

    • In the Explorer, right-click a join link and select Properties.

      or

    • Double-click a join link in the Explorer.

    The Join Properties dialog box opens.

  2. Select the Left Outer Join option button.
  3. Click OK.
x
Procedure: How to Add Information About Fields to Join Field Lists

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.

  1. With the Join tool open, right-click within a table to view available field options. To change information displayed for tables globally, select Options from the Window menu. Click the Reporting tab and select the appropriate options from the Field List Options area.
  2. Select any or all of the following items: Qualified Names, Name, Alias, Title, Description, Format. A check mark displays next to your selections. Name and Descriptions are checked by default.

    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.

  3. Expand the window to see the additional field list columns. Click on column titles if you want to change the sort order.
Top of page
x
Saving a Join

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.

Note: You may also select Save from the Developer Studio File menu, or select the Save icon from the Join toolbar.
Example: Rearranging Saved Joins

The Join tool preserves the table sizing and positioning when you re-open the tool. The Join tool displays Joins that use some of the same files in one panel of the Join window. The following two Joins share a host file:

Rearranging Saved Joins

Tip: If the field names you wish to join are different, you must drag and drop the field names to make the connection.

Note: If you close the Join window and then reopen it, the two Joins appear in the same panel.

Join window

Top of page
x
Customizing a Join

How to:

Reference:

You can customize an existing Join by changing the files or fields involved. If no default Join was created, you can use the same technique to specify a connection explicitly.

You can also change the Join name, make the Join a single instance, use a virtual field as the Join field, and add tag names to the Join files.

x
Procedure: How to Remove a File From the Join
  1. Click the table of the file you want to remove.
  2. Click the Delete button on the toolbar or press the Delete key on your keyboard. A confirmation dialog appears.

If you delete the host file, all cross-referenced files are automatically deleted.
x
Procedure: How to Delete a Join

To delete the existing Join:

  1. Right-click the Join connector line.
  2. Select Delete from the context menu.

You can also select the Join connector line and click the Delete button on the toolbar, or press the Delete key on the keyboard.

The files remain in the window with no connector line between them.
x
Procedure: How to Specify Join Fields

To create your own Join (after deleting the existing Join, if necessary):

  1. Select a field in the host file panel.
  2. Click and hold the selected field.
  3. Drag the selected field from the host file panel onto the matching field located in the cross-referenced file panel. The cursor changes and displays the appropriate icon when a join is possible.
  4. Release the mouse button.

A Join connector line appears.
x
Procedure: How to Create Additional Joins

You can leave the Join window open and create another Join by:

You can define up to 63 concurrent Joins that will have a maximum of 64 segments in the new join structure.
x
Procedure: How to Make a Join a Single Instance

By default, each Join is a multiple instance.

To make the Join a single instance:

  1. Right-click the Join connector line.
  2. Select Single Instance from the context menu.
x
Reference: Usage Notes for Specifying Join Fields
Top of page
x
Changing the Join Name

How to:

A default name is automatically assigned to each Join.

You can use the Join name to later clear the Join and to prevent another Join from overwriting it. If you remove the Join name completely, any subsequent unnamed Join will overwrite this Join.

x
Procedure: How to Change or Remove the Default Join Name
  1. Right-click the Join connector line and select Join Name from the context menu. The Join Name dialog box displays the default Join name.
  2. Edit or delete the name in the Join Name box and click OK.

    You can also provide a description. This is not used in the Join command. It is used for reference purposes. A comment (-*) is added to the procedure. For example, -*JOINDESC J0.

Note: The following words cannot be used as a Join name: ALL, AND, AS, IN, JOIN, TAG, TO, WITH.
x
Procedure: How to Display Current Join Structures

To display a list of joined data source issue the command from the Command Console: 

? JOIN

This displays every Join currently in effect.

WebFOCUS