Using Joined Files

The FOCUS command JOIN allows you to link related files by a common field.   Joined files act as a single integrated data structure allowing you to report on two or more files as if they are a single file. Due to security issues, this command has been turned off for CIRS users.  However, to assist your report writing, HR-ISA has developed automatic joins and pre-programmed joins.  To merge data from two or more files that are not automatically joined or that do not have a pre-programmed join, use the MATCH command.  Refer to your FOCUS documentation for information.

 

Automatic Joins

All of the current status files (i.e., AC, SAC, AN and SAN) have fields that are linked to fields in cross-referenced files.  Cross-referenced files are single segment files that primarily contain information for coded fields in the main file.

 

For example, the field AC:AGENCY in the Active Current Status file is linked to the field AGENCY in the Agency/Campus/Fund file.   

 

To use an automatic join:

  • Refer to the file descriptions to identify files automatically joined.  If the file you are using is automatically joined to a file containing the needed data, simply use the fieldname from the cross-referenced file in your report request.  

  • The file name specified for the TABLE FILE command is the current status file (i.e., AC, SAC, AN and SAN).  

  • Be sure to use the correct file prefix (if any) when specifying fieldnames in the joined file.  

  • Cross-referenced files are automatically allocated when any other file is selected for Online or Batch execution.  

 

Pre-programmed Joins

A series of pre-programmed joins are available to link frequently used combination of files.  All of the major files in CIRS have pre-programmed joins to other files.  When a pre-programmed join is executed, it links the files and executes the standard defines for the joined files. The naming convention for all pre-programmed joins is as follows: (Join) (Host File) (Cross-Referenced File).

 

For example, the pre-programmed join JACPS links records in the Active Current Status file to records in the Pay Scales file by Class Code and Range Code, and executes the defines for both files.  

 

To use a pre-programmed join:

  • Refer to the file descriptions to identify available pre-programmed joins.   If available, execute the join name in place of the standard defines.  

  • The file name specified for the TABLE FILE command is the HOST file.  

  • If you are using an external file (i.e., A54), the following screening statements must also be included in your report request:  IF XX:CLASS GT 0 and IF XX:RANGE GE 0.  

  • Be sure to use the correct file prefix when specifying fieldnames in the joined file.  

  • When executing the report request, you must select the major file(s) specified in the join.   Any cross-referenced files included in the join will be automatically allocated when the other files are selected.