Using Joins

When data is needed from one or more CIRS files, a join allows you to link related files by a common field. CIRS provides two types of joins for you to use:  Automatic and pre-programmed.

Automatic Joins

All of the current status files (i.e.,  AC, SAC, AN, SAN) are automatically joined to cross-referenced files.  Cross-referenced files are single segment files that primarily contain information for coded fields (e.g., campus name).  Refer to the file descriptions in the CIRS Data Element Dictionary to identify files automatically joined.  

 

To use fields from automatically joined files, simply specify the fieldname in the report request.   

For example, the report below prints the employee name from the Active Current Status file and degree name from the Degree Level file:

EX AC

TABLE FILE AC

PRINT AC:WNAME DEGREE

END

Pre-programmed Joins

All of the major files in CIRS have pre-programmed joins to the Pay Scales file and/or the  Agency Campus Fund file.  Refer to the file descriptions in the CIRS Data Element Dictionary to identify available joins.   

 

To use a pre-programmed join, execute the join name in place of the standard defines. Be sure to use the correct file prefix when specifying fieldnames in the joined file.  If using an external file (TR, STR, EH, PH, PHS, PY or A54)  the following screening statements must also be included in your report request:  IF XX:CLASS GT 0 and  IF XX:RANGE GE 0.     

 

For example, the report below prints the employee name from the Transaction Data file and class title from the Pay Scales file:

 

EX JTRPSAG

TABLE FILE TR

PRINT TR:WNAME PS:TITLE

WHERE TR:CLASS GT 0

WHERE TR:RANGE GE 0

END  

 

Note:  Cross-referenced and 'hidden' files are automatically allocated when any other file is selected for Online or Batch execution.  If a join is not available, the MATCH command can be used to merge data from two or more files.  For more information on the MATCH and JOIN command, refer to your FOCUS Documentation.