The JOIN Command

With the JOIN command, you can link two or more Master Files together for reporting purposes. The connected Master Files are treated as one structure even though they remain physically separate. Master Files stay joined for the duration of your session or until you issue the JOIN CLEAR command.

When you join two data sources, some records in one of the files may lack corresponding records in the other file. When a report omits records that are not in both files, the join is called an inner join. When a report displays all matching records, plus all records from the host file that lack corresponding cross-referenced records, the join is called a left outer join.

The SET ALL command globally determines how all joins are implemented. If the SET ALL=ON command is issued, all joins are treated as outer joins. With SET ALL=OFF, the default, all joins are treated as inner joins.

Each JOIN command can specify explicitly which type of join to perform, locally overruling the global setting. If you do not specify the type of join in the JOIN command, the ALL parameter setting determines the type of join to perform.

You can also join data sources using one of two techniques for determining how to match records from the separate data sources. The first technique is known as an equijoin and the second is known as a conditional join. When deciding which of the two join techniques to use, it is important to know that when there is an equality condition between two data sources, it is more efficient to use an equijoin rather than a conditional join.

You can use an equijoin structure when you are joining two or more data sources that have two fields, one in each data source, with formats (character, numeric, or date) and values in common. Joining a product code field in a sales data source (the host file) to the product code field in a product data source (the cross-referenced file) is an example of an equijoin.

The conditional join uses WHERE-based syntax to specify joins based on WHERE 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 could join an employee ID in the host file to an employee ID in the cross-referenced file to determine which employees took training courses in a given date range (the WHERE condition).

Joins can also be unique or non-unique. A unique, or one-to-one, join structure 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 equijoin structure.

A non-unique, or one-to-many, join structure matches one value in the host data source to multiple values in the cross-referenced field. Joining an employee ID in a company's employee data source to an employee ID in a data source that lists all the training classes offered by that company results 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.

You can join the Master Files describing any of these data sources to that of your IDMS data source:

An equijoin structure is implemented by matching one field that is common to both data sources. The fields on the IDMS target file can be:

In the Master File, the names of common fields can differ, but their field formats (ACTUAL and USAGE) must be the same.

Using an Equijoin

How to:

Reference:

The most common joined structures are based on real fields that have been declared in the Master Files of the host and cross-referenced data sources, respectively.

Top of page

Syntax: How to Join Two Data Sources

JOIN [LEFT_OUTER|INNER]  field1 [WITH rfield] IN hostfile [TAG tag1]
TO [ALL] field2 IN crfile [TAG tag2] [AS name]
[END]

where:

field1, field2

Are the fields common to both Master Files.

WITH rfield1

Use only if field1 is a virtual field. Assigns a logical home with a real field in the host file.

hostfile

Is the host Master File.

TAG tag1

Is a tag name of up to eight characters (usually the name of the Master File), which is used as a unique qualifier for fields and aliases in the host file.

The tag name for the host file must be the same in all the JOIN commands of a joined structure.

ALL

Use if non-unique relationships exist in the target file.

crfile

Is the target or cross-referenced Master File.

TAG tag2

Is a tag name of up to eight characters (usually the name of the Master File), which is used as a unique qualifier for fields and aliases in the cross-referenced file. In a recursive joined structure, if no tag name is provided, all field names and aliases are prefixed with the first four characters of the join name.

AS name

Assigns a name to the JOIN structure. You must assign a unique name to a join structure if:

You want to ensure that a subsequent JOIN command will not overwrite it.
You want to clear it selectively later.
The structure is recursive, and you do not specify tag names.

END: Required when the JOIN command is longer than one line. Terminates the command.

To join more than two files as a single structure, indicate the common fields as follows:

JOIN field1 IN file1 TO field2 IN file2 AS name1 
JOIN field3 IN file1 TO field4 IN file3 AS name2

Top of page

Example: Reporting From a Joined Structure

This example joins the FOCUS data source JOBFILE to the IDMS EMPFULL data source based on job codes. First the JOBCODE field in JOBFILE is edited to make it compatible with the JOB_ID field in EMPFULL. The JOIN command is issued prior to the DEFINE. If the DEFINE were issued first, it would be cleared by the JOIN command:

JOIN JOBID WITH JOBCODE IN JOBFILE TO                 
ALL JOB_ID IN EMPFULL AS J1                           
END                                                   
DEFINE FILE JOBFILE                                   
JCODE/A2 = IF JOBCODE LIKE 'A__' THEN '10' ELSE '20'; 
JOBID/A4 = JCODE|EDIT(JOBCODE,'$99');                 
END                                                   
TABLE FILE JOBFILE                                    
SUM EMP_NAME IN 25 TITLE                            
BY DEPT_NAME                                          
END

The output is:

DEPT_NAME               EMP_NAME                   TITLE             
---------               --------                   -----             
ACCOUNTING AND PAYROLL  RUPERT    JENSON           MGR ACCTNG/PAYROLL
PERSONNEL               ELEANOR   PEOPLES          MGR PERSONNEL

Top of page

Example: Reporting With a Left Outer Join

The following request creates a left outer join between the JOBFILE and EMPFULL data sources based on job codes. First the JOBCODE field in JOBFILE is edited to make it compatible with the JOB_ID field in EMPFULL. The JOIN command is issued prior to the DEFINE. If the DEFINE were issued first, it would be cleared by the JOIN command:

JOIN LEFT_OUTER JOBID WITH JOBCODE IN JOBFILE TO      
ALL JOB_ID IN EMPFULL AS J1                          
END                                                   
DEFINE FILE JOBFILE                                   
JCODE/A2 = IF JOBCODE LIKE 'A__' THEN '10' ELSE '20'; 
JOBID/A4 = JCODE|EDIT(JOBCODE,'$99');                 
END                                                   
TABLE FILE JOBFILE                                    
PRINT JOBCODE JOB_ID JOBID                            
END

The output contains all job codes in the JOBFILE data source. Where no matching records exist in the EMPFULL data source, default values (the missing data symbol) are displayed. The output is:

JOBCODE  JOB_ID  JOBID
-------  ------  -----
A01      1001    1001 
A02      .       1002 
A07      .       1007 
A12      .       1012 
A14      .       1014 
A15      .       1015 
A16      .       1016 
A17      .       1017 
B01      2001    2001 
B02      .       2002 
B03      .       2003 
B04      .       2004 
B14      .       2014

The following is the same request using an inner join:

JOIN JOBID WITH JOBCODE IN JOBFILE TO                
ALL JOB_ID IN EMPFULL AS J1                         
END                                                  
DEFINE FILE JOBFILE                                  
JCODE/A2 = IF JOBCODE LIKE 'A__' THEN '10' ELSE '20';
JOBID/A4 = JCODE|EDIT(JOBCODE,'$99');                
END                                                  
TABLE FILE JOBFILE                                   
PRINT JOBCODE JOB_ID JOBID                           
END

The output contains only those records that have matching values in both data sources. The output is:

JOBCODE  JOB_ID  JOBID
-------  ------  -----
A01      1001    1001 
B01      2001    2001

Top of page

Reference: Usage Notes for the JOIN Command

Up to 1023 joins may be active in one session.
For a FOCUS target file, field2 must be indexed (FIELDTYPE=I).
If you intend to use a virtual field as field1, specify its field name in the JOIN command and then issue its DEFINE command. Any DEFINE commands issued prior to the JOIN are cleared, unless the KEEPDEFINES parameter is set ON. For information on ways of keeping DEFINE commands active during a JOIN, see the Creating Reports manual.
If you know that the target field has a unique relationship to the source file, omit the ALL in the JOIN command. Omitting ALL reduces I/O overhead.
To display the JOIN structure, use the CHECK FILE command and specify the name of the host file.

Top of page

Reference: Usage Notes for JOIN LEFT_OUTER/INNER Command Syntax

The SET ALL and SET CARTESIAN commands are ignored by the syntax.
The ALL. parameter is not supported. If the ALL. parameter is used, the following message displays:
```
(FOC32452) Use of ALL. with LEFT_OUTER/INNER not allowed
```
If you define multiple joins, the resulting structure can be a single path or multi-path data source.
- If the setting MULTIPATH=SIMPLE is in effect and the report is based on multiple paths, each of the individual joins is constructed separately without regard to the other joins, and the matching records from each of the separate paths displays on the report output. Therefore, the output can contain records that would have been omitted if only one of the joins was in effect.
- If the setting MULTIPATH=COMPOUND is in effect with a multi-path report, or if the report displays data only from a single path, the report output displays only those records that satisfy all of the joins.

Top of page

Syntax: How to List JOIN Structures

To list your JOIN structures, enter:

? JOIN

Top of page

Syntax: How to Clear JOIN Structures

To clear a specific JOIN structure, specify the name that you assigned to the join:

JOIN CLEAR name

Top of page

Syntax: How to Clear All JOIN Structures

To clear all structures, use an asterisk (*) instead of a join name:

JOIN CLEAR *

For more information about JOIN commands, refer to your FOCUS documentation.

Using a Conditional Join

How to:

Create a Conditional JOIN

Using conditional JOIN syntax, 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 FOCUS and for VSAM, ADABAS, IMS, IDMS and all relational data sources. Because each data source differs in its ability to handle complex WHERE criteria, the optimization of the conditional JOIN syntax differs depending on the specific data sources involved in the join and the complexity of the WHERE criteria.

The standard ? JOIN command lists every join currently in effect, and indicates any that are based on WHERE criteria.

Top of page

Syntax: How to Create a Conditional JOIN

The syntax of the conditional (WHERE-based) JOIN command is

JOIN [LEFT_OUTER|INNER] FILE hostfile AT hfld1 [WITH hfld2] [TAG tag1]
     TO {UNIQUE|MULTIPLE} 
     FILE crfile AT crfld [TAG tag2] [AS joinname]
     [WHERE expression1;
     [WHERE expression2;
     ...]
END

where:

INNER

Specifies an inner join. If you do not specify the type of join in the JOIN command, the ALL parameter setting determines the type of join to perform.

LEFT_OUTER

Specifies a left outer join. If you do not specify the type of join in the JOIN command, the ALL parameter setting determines the type of join to perform.

hostfile

Is the host Master File.

AT

Links the correct parent segment or host to the correct child or cross-referenced segment. The field values used as the AT parameter are not used to cause the link. They are simply used as segment references.

hfld1

Is the field name in the host Master File whose segment will be joined to the cross-referenced data source. The field name must be at the lowest level segment in its data source that is referenced.

tag1

Is the optional tag name that is used as a unique qualifier for fields and aliases in the host data source.

hfld2

Is a data source field with which to associate a DEFINE-based conditional JOIN. For a DEFINE-based conditional join, the KEEPDEFINES setting must be ON, and you must create the virtual fields before issuing the JOIN command.

MULTIPLE

Specifies a one-to-many relationship between from_file and to_file. Note that ALL is a synonym for MULTIPLE.

UNIQUE

Specifies a one-to-one relationship between from_file and to_file. Note that ONE is a synonym for UNIQUE.

Note: UNIQUE returns only one instance and, if there is no matching instance in the cross-referenced file, it supplies default values (blank for alphanumeric fields and zero for numeric fields).

crfile

Is the cross-referenced Master File.

crfld

Is the join field name in the cross-referenced Master File. It can be any field in the segment.

tag2

Is the optional tag name that is used as a unique qualifier for fields and aliases in the cross-referenced data source.

joinname

Is the name associated with the joined structure.

expression1, expression2

Are any expressions that are acceptable in a DEFINE FILE command. All fields used in the expressions must lie on a single path. Note that performance is enhanced when the expression is against an IDMS index or CALC field.

END

The END command is required to terminate the command and must be on a line by itself.

Note: Single line JOIN syntax is not supported.

Top of page

Example: Using a Conditional Join

The following example joins the JOBFILE and EMPFULL data sources on the condition that the job code in JOBFILE starts with the character A.

The following request creates the JOBID field. Note that the virtual field must be created before the join is issued, and that the KEEPDEFINES parameter must be set ON:

SET KEEPDEFINES=ON                                   
DEFINE FILE JOBFILE                                  
JCODE/A2 = IF JOBCODE LIKE 'A__' THEN '10' ELSE '20';
JOBID/A4 = JCODE|EDIT(JOBCODE,'$99');                
END                                                  
JOIN FILE JOBFILE AT JOBID WITH JOBCODE TO ALL       
     FILE EMPFULL AT JOB_ID    TAG M1 AS JW1        
  WHERE JOBCODE LIKE 'A__';                          
END                                                  
TABLE FILE JOBFILE                                   
SUM EMP_NAME IN 30 JOBCODE JOB_ID JOBID     
BY DEPT_NAME                                         
END

The output is:

DEPT_NAME                    EMP_NAME                  JOBCODE JOB_ID JOBID
---------                    --------                  ------- ------ -----
ACCOUNTING AND PAYROLL       BRIAN     NICEMAN         A17     2051   1017
BLUE SKIES                   DANIEL    MOON            A17     5037   1017
BRAINSTORMING                RICHARD   WAGNER          A17     5029   1017
COMPUTER OPERATIONS          GEORGE    FONRAD          A17     3051   1017
EXECUTIVE ADMINISTRATION     THEMIS    PAPAZEUS        A17     9007   1017
INTERNAL SOFTWARE            RALPH     TYRO            A17     3027   1017
PERSONNEL                    MADELINE  ORGRATZI        A17     1023   1017
PUBLIC RELATIONS             MONTE     BANK            A17     4001   1017
THERMOREGULATION             MARK      TIME            A17     5033   1017