Join Tab

In this section:

You can use the Join tab to manipulate data sources when using a join. The Join tab is available only when you are creating a Join.

A join is a temporary connection between two or more data source files that share at least one common field. For example, a Human Resources department could have an EMPLOYEE data source that includes a job code field, and a JOBFILE data source that also includes a job code field. In this scenario, the two data sources could be joined.

When creating a join, 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), the matching records from the second file (cross-referenced file) are also retrieved. The Join tab is shown in the following image.

Join tab


Top of page

x
Windows Group

You can choose how to display the data sources currently in the join. The Windows group is shown in the following image.

Windows group

The commands are:

Cascade

Arranges the windows as overlapping tiles (default).

Horizontal

Arranges the windows as horizontal tiles that do not overlap.

Vertical

Arranges the windows as vertical tiles that do not overlap.


Top of page

x
Join Group

In this section:

You can save, run, add, remove, and automatically join data sources when creating a Join. The Join group is shown in the following image.

Join group

The commands are:

New

Creates a new join.

Add

Adds a new file to the join.

Clear

Launches the Join Clear dialog box, where you can specify the available joins to clear.

Define

Inserts a Define field into the Join.

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 can contain only fields in the host file, and constants. It cannot 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, with each command referring to the same host file.

For more information, see Define-Based Joins.

Where/If

Creates a conditional Join.

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

Show/Hide

Shows or hides the .fex source window.

Auto Join

Performs automatic joins within tables.



x
Creating a Join

You can use the Join canvas to temporarily join two or more data sources and use data from them as if they were one data source. Joined files remain physically separate, but are treated as one data source. The Join component provides a graphical method for creating and manipulating all types of Joins. To create a Join, you must specify a host file, and then a cross-referenced file. Both host files and cross-referenced files are Master Files.

You are responsible for placing the Join object in the correct position within your procedure (that is, before a Define, Report, or Chart), and running it. The Join remains active for the entire session, unless you explicitly clear it.



x
Procedure: How to Join Data Sources
  1. Open an existing procedure, or create a new procedure.
  2. In the Procedure View panel, right-click the procedure folder or the component that you want to create the Join after, point to New and click Join.
  3. Select a Master File (host file) and click OK.

    The Join canvas opens and displays the fields in the Master File.

  4. On the Join tab, in the Join group, click Add.

    You can also right-click the Join canvas and select Add File.

  5. Select a Master File (cross-referenced file) and click OK.

    The new Master File is added to the canvas. Common fields in both files are joined by a Join connector line.

  6. Click Save.
  7. Click Run.

    A dialog box opens, displaying the component code, and either an error message or text stating that no error exists.

  8. Click OK and close the Join canvas.

    You can add another component to the procedure by right-clicking the Join component, pointing to New, and clicking the component. When you are prompted to select a Master File, select the same host file you used to create the Join component. This allows you to use the joined data sources.



x
Define-Based Joins

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 filed. The Define expression that creates the virtual host field may contain fields that only appear in the host file or constants. It cannot contain fields that are cross-referenced.

A virtual field is a temporary field that you describe in a Master File or procedure. The value of the virtual field is calculated and treated as part of the data source.

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.

Consider the following factors before making a virtual field the host field in a Join:

  • If you create the virtual field in a procedure rather than in the Master File, you must make sure the virtual field runs after the Join runs, because a Join clears all existing virtual fields. You can also use the SET KEEPDEFINE=ON expression.
  • You must delete the existing Join connector line, if there is one, before creating the Join on the virtual field. You cannot join multiple fields with this option. The virtual field must be the only Join field.


x
Procedure: How to Use a Virtual Field as the Join Field
  1. Open an existing procedure, or create a new procedure.
  2. In the Procedure View panel, right-click the procedure folder or the component that you want to create the Join after, point to New, and click Join.
  3. Select a Master File (host file) and click OK.

    The Join canvas opens and displays the fields in the Master File.

  4. On the Join tab, in the Join group, click Define.

    The Join Define in File dialog box opens.

  5. You must associate the virtual field with a segment of the data source.
    1. Type the name of the virtual field you are going to define in the Define Field box.
    2. In the With Field drop-down list box, choose the name of any real field from the segment in which you want to put the virtual field, and click OK.

      The virtual field appears in the Fields window of the host file.

  6. On the Join tab, in the Join group, click Add.

    You can also right-click the Join canvas and select Add File.

  7. Select a Master File (cross-referenced file), and click OK.

    The new Master File is added to the canvas. Delete any existing Join connector lines.

  8. To complete the Join, drag the mouse pointer from the virtual field to a matching field in the cross-referenced file.

    You cannot join multiple fields with this option. The virtual field must be the only Join field.



x
Creating Multi-Field Joins

In some cases, you might want to Join more than one host field to a single cross-referenced field. The procedure, as well as the relationship, differs for Joins between two relational data sources, Joins between two WebFOCUS data sources, and Joins between relational and WebFOCUS data sources.

There are two types of multi-field Joins:

The following table lists the types of Joins allowed between files:

Host

Cross-Referenced

Supported Multi-Field Join Constructions

SQL*

SQL

You can Join two SQL files with a multi-field construction. You cannot directly Join two SQL files with a concatenated Join. You can, however, use a Define-based Join to achieve the same result that a concatenated Join would produce between two SQL files.

WebFOCUS

WebFOCUS

You can use only the concatenated Join construction to Join two WebFOCUS host fields to a single WebFOCUS cross-referenced field.

SQL

WebFOCUS

You can use only the concatenated Join construction to Join two SQL host fields to a single WebFOCUS cross-referenced field.

WebFOCUS

SQL

You can Join a WebFOCUS file and an SQL file with multi-field Join construction. You cannot Join a WebFOCUS file and an SQL file with a concatenated Join. You can, however, use a Define-based Join to achieve the same result that a concatenated Join would produce between these files.

* SQL represents all relational data sources.



x
Creating a Concatenated Join

You can use the concatenated Join construction between two WebFOCUS files and between a host relational file and a cross-referenced WebFOCUS file. This is the only type of multi-field Join construction you can use between these files.

The first two host fields are combined in a concatenated Join. The resulting field is then joined to the cross-referenced field.



x
Using Conditional Joins

Using conditional Joins, you can establish Joins based on conditions other than equality between fields. In addition, the host and cross-referenced Join fields do not have to contain matching formats and the cross-referenced field does not have to be indexed.

The conditional Join is supported for WebFOCUS and all relational data adapters. Because each data source differs in its ability to handle complex conditional criteria, the optimization of the WHERE syntax differs depending on the specific data sources involved in the Join and the complexity of the conditional criteria.

To display a list of joined data sources, select the following query subject in the Query tool, or issue the command from the Command Console:

? JOIN

This query displays every Join currently in effect and indicates any that are based on conditional criteria.



x
Procedure: How to Create Conditional Joins
  1. Open an existing procedure, or create a new procedure.
  2. In the Procedure View panel, right-click the procedure folder or the component that you want to create the Join after, point to New, and click Join.
  3. Select a Master File (host file) and click OK.

    The Join canvas opens and displays the fields in the Master File.

  4. On the Join tab, in the Join group, click Add.

    You can also right-click the Join canvas and select Add File.

    If you are developing in the Data Servers area or in the Repository, a WebFOCUS Table List dialog box opens. If you are developing in the Local Projects area, the Open dialog box opens.

  5. Select a Master File (cross-referenced file) and click OK.

    The new Master File is added to the canvas. Common fields in both files are joined by a Join connector line.

  6. Select the Join (the connector line), and on the Join tab, in the Job group, click Where/If. The Where Expression Builder dialog box opens.
  7. After you have specified your WHERE criteria, click OK to return to the Join tool.

    If you need to change the Join type or Join name, double-click the conditional Join to launch the Join Properties window.



x
Customizing a Join

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. Press the Delete key. A confirmation dialog box appears.

    If you delete the host file, all cross-referenced files are automatically deleted.



x
Procedure: How to Delete a Join
  1. Right-click the Join connector line.
  2. From the shortcut menu, click Delete.

    You can also select the Join connector line, and on the Join tab, in the Join group, click Clear, or press the Delete key.

    The files remain on the canvas 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 pointer 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:

  • Clicking New on the ribbon.
  • Clicking New Join from the shortcut menu on the Join canvas.

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. From the shortcut menu, click Single Instance.


x
Changing the Join Name

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 from the shortcut menu, click Join Name. 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, but 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 sources, issue the command from the Command Console:

? JOIN

This command displays every Join currently in effect.



x
Reference: Join Define in File Dialog Box

The Join Define in File dialog box has the following fields and options:

Define Field

Identifies the virtual field that is the host field in the Join.

With Field

Associates the virtual field with a segment in the data source. Select any field from the segment in which you want to place the virtual field.



x
Adding a Tag Name

Tag names provide a way of making identical field names a single instance. They function as aliases for the file names in the Join. Tag names are useful when you join a file to itself because in such a Join all field names, segment names, and file names are the same in the host and cross-referenced files. Tag names are also useful if a file is used as a target more than once in the join structure.

A procedure can use the tag name of a file as a qualifier for field names and aliases in the file.

If you create a report using a recursive Join without tag names, the field names in the cross-referenced file are prefixes with the first four characters of the Join name.



x
Procedure: How to Add a Tag Name
  1. Double-click the connector line between the tables. The Join Properties dialog box opens. The Join Properties dialog box also opens if you right-click a selected link, and click Properties on the shortcut menu. A thick line appears when a Join is selected.
  2. Enter names in the Host Tag and Cross Tag name fields and click OK.

    Note:

    • The following words cannot be used as a tag name: ALL, AND, AS, IN, JOIN, TAG, TO, WITH.
    • Tag names can be up to eight characters in length.



x
Working With Joins

After a Join exists, you can retrieve it, report from it, view its syntax, and clear it. Clearing a Join makes it inactive.



x
Reference: Join Clear Dialog Box

The Join Clear dialog box has the following fields and options:

Available Joins

Lists all current Joins.

Joins To Clear

Displays the Joins that will be deleted when you click OK.

Double Right Arrow

Moves the selected Joins to the Joins to Clear list.

Double Left Arrow

Moves the selected Joins to the Available Joins list.



x
Reference: Join Properties Dialog Box

To view or modify the properties of a Join, right-click the Join link and select Properties, or double-click the Join link. The Join Properties window appears, as shown in the following image.

Join Properties

The Join Properties window has the following fields and options:

Host Tag

Enter a tag name for the data source.

Cross Tag

Enter a tag name for the cross-referenced file.

Selected Fields

Displays the field that has been joined from the host file to the cross-referenced file. A single arrow indicates a unique Join, and a double-arrow indicates a non-unique Join.

Join Name

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.

Description

Enter a description. This is not used in the Join command. It is used for reference purposes. A comment (-*) is added to the procedure.

Join Type

Indicates the type of Join. Choose from the following:

Multiple Instance (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 an 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. This is the default selection.

Single Instance (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.

Unspecified. This option is selected in the Join properties when a Join that was created in an earlier release is opened in the Join tool. In earlier versions, 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.


WebFOCUS