Creating Multi-Field Joins

In this section:

In some cases, you may 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 FOCUS data sources, and joins between relational and FOCUS 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. For more information on using a Define-based join, see Customizing a Join.

FOCUS

FOCUS

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

SQL

FOCUS

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

FOCUS

SQL

You can join a FOCUS file and an SQL file with multi-field join construction. You cannot join a FOCUS 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. For more information on using a Define-based join, see Customizing a Join.

* SQL represents all relational data sources.



Example: Creating a Multi-Field Join for Relational Data Sources

This example uses two relational data sources: ECOURSE, the host file, and COURSE, the cross-referenced file. ECOURSE contains the fields COURSE_NAME and EMP_ID, while COURSE also contains the fields COURSE_NAME and EMP_ID. These fields have the same format.

Creating a Mult-Field Join

To join the COURSE_NAME and EMP_ID host fields to their corresponding cross-referenced fields, you would join each host field separately to the cross-referenced field. See Creating a Join With Graphical Tools for the procedure to join a host field to a cross-referenced field. This procedure results in the following join structure.

join structure

This join construction represents one join. Developer Studio retrieves matching records across this join only when:


Top of page

x
Creating a Concatenated Join

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

In a concatenated join, first the two host fields are combined, then the resulting field is joined to the cross-referenced field.



Example: Creating a Concatenated Join for FOCUS Data Sources

This example uses two FOCUS files: EMPLOYEE, the host file, and HOBBIES, the cross-referenced file. EMPLOYEE contains the fields FIRST_NAME and LAST_NAME, and HOBBIES contains the field FULL_NAME.

To join the host fields to the cross-referenced field:

  1. Click FIRST_NAME and then click LAST_NAME in the EMPLOYEE file.

    Both field names are highlighted.

  2. Click and hold the FIRST_NAME field.
  3. Drag the mouse until you position the Join icon over the FULL_NAME field in the cross-referenced file. The mouse cursor changes when you drop or link a field.
  4. Release the mouse button.

    The concatenated host field is joined to the cross-referenced field FULL_NAME. Developer Studio graphically represents this join, as shown in the following image.

    concatenated host field joined to a cross-referenced field

    The fields FIRST_NAME and LAST_NAME are not joined to FULL_NAME, rather FOCUS joins the virtual field created from the concatenation of FIRST_NAME and LAST_NAME to FULL_NAME.


WebFOCUS