Including Data From More Than One File: JOIN

Topics:

Issuing a JOIN Command

Creating Basic Output Requests illustrated how to access data from a data source. The JOIN command enables you to report from two or more data sources, as if they were one. Although the two data sources remain physically separate, it will seem as if you are working with one data source. When two data sources are joined, one is called the host file, the other is called the cross-referenced file. Each time a record is retrieved from the host file, the corresponding fields in the cross-referenced file are identified, if they are referenced in the report request. The records in the cross-referenced file containing the corresponding values are then retrieved. You can establish a joined structure in several ways. Each way requires specific conditions.

Issuing a JOIN Command

The format for a simple JOIN command is:

JOIN field IN host TO [ALL] field IN crfile AS joinname

The fields from the host file and cross-referenced file (crfile) must have the same format. The JOIN command requires a real field as the target (cross-referenced field). ALL retrieves multiple matching records from the cross-referenced file. Without ALL, only a single value is retrieved. The AS phrase is a name to identify the Join.

For example, both the EMPLOYEE data source and the JOBFILE data source contain a JOBCODE field. The JOBFILE data source contains a description for each of the job codes (JOB_DESC field). To join the EMPLOYEE data source to the JOBFILE data source, issue the following JOIN command:

JOIN JOBCODE IN EMPLOYEE TO JOBCODE IN JOBFILE AS JJOIN

To retrieve the job description for each of the job codes for each employee, issue the following request:

TABLE FILE EMPLOYEE
BY LAST_NAME BY JOBCODE BY JOB_DESC
END

Run the request. The output is:

Note: Joining two separate structures may result in duplication of field names. To identify which field name is being referenced in a request, qualify it as FILENAME.fieldname, SEGNAME.fieldname or FILENAME.SEGNAME.fieldname, to uniquely identify which field name instance is to be used. Without qualification, any field name reference will find the FIRST matching field name within the structure, which may not be what was intended.

	WebFOCUS