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.

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

• Other IDMS data sources (SUFFIX=IDMSR)
• FOCUS
• VSAM or ISAM or QSAM
• SQL or UDB (DB2^®)
• DOS/DL1 or IMS™
• CA-Datacom^®/DB
• MODEL 204^®
• Fixed- format sequential

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

• An IDMS CALC field on a network record-type.
• An indexed field (FIELDTYPE=I) on a network record-type.
• A field on an LRF record.

The fields on the host file can be:

• A virtual field located in a host Master File or created as a separate command.
• Any field.

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

Syntax: How to Join Two Data Sources

JOIN 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

Reference: Usage Notes for the JOIN Command

• Up to 16 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.
• If you know that the target file is unique, 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.

Syntax: How to List JOIN Structures

To list your JOIN structures, enter:

? JOIN

Syntax: How to Clear JOIN Structures

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

JOIN CLEAR name

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.

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