Features Added in FOCUS 7.6.2

Starting with FOCUS 7.6.2, a comma-delimited file can be the host or cross-referenced file in a JOIN command and DEFINEs that have the missing attribute on either side of the equal sign can be compiled.

Joining Comma-Delimited Files

How to:

Specify a JOIN

Reference:

Usage Notes for Comma-Delimited Files in a Join

Starting in FOCUS 7.6.2, a comma-delimited file can be the host or cross-referenced file in a JOIN command. The setting PCOMMA = ON must be in effect. Therefore, alphanumeric fields must be enclosed in double quotation marks and all of the data must be on a single line, with the line-end character indicating the end of record. This format is consistent with a HOLD file created as format COM or COMT. Note that files created by HOLD FORMAT COM or HOLD FORMAT COMT are created using the PCOMMA ON format, and thus PCOMMA must be set to ON in order to issue a report request against these files.

In comma-delimited files, the cross-referenced field can be any field. However, both the host and cross-referenced files must be retrieved in ascending order of the concatenated join fields.

Top of page

Syntax: How to Specify a JOIN

JOIN [LEFT_OUTER|INNER] field1 [AND field1a...] IN host [TAG tag1]
 TO [ALL|MULTIPLE|UNIQUE] field2 [AND field2a...] IN crfile [TAG tag2]
[AS joinname]
END

where:

field1

Is the name of a field in the host file containing values shared with a field in the cross-referenced file. This field is called the host field.

field1a...

Can be up to three additional fields in the host file. The phrase beginning with AND is required when specifying multiple fields.

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.

host

Is the name of the host file.

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|MULTIPLE|UNIQUE]

Specifies whether a join is one-to-one or one-to-many.

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).

Use the MULTIPLE parameter when the cross-referenced field may have multiple instances in common with one value in the host file. Note that ALL is a synonym for MULTIPLE, and omitting this parameter entirely is a synonym for UNIQUE

Note: Many-to-many joins are not supported when the cross-referenced file is sequential.

field2

Is the name of a field in the cross-referenced file containing values that match those of field1 (or of concatenated host fields). This field is called the cross-referenced field and can be any field as long as the both data sources are in ascending order of the concatenation of join fields.

field2a...

Are the names of up to three fields in the cross-referenced file with values in common with their corresponding join fields in the host file.

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 join structure, if no tag name is provided, all field names and aliases are prefixed with the first four characters of the join name.

joinname

Is an optional name of up to eight characters that you may assign to the join structure. You must assign a unique name to a join structure if:

You want to ensure that a subsequent JOIN command does not overwrite it.
You want to clear it selectively later.
The structure is recursive.

Note: If you do not assign a name to the join structure with the AS phrase, the name is assumed to be blank. A join without a name overwrites an existing join without a name.

END

Is required when the JOIN command is longer than one line; it terminates the command.

Top of page

Reference: Usage Notes for Comma-Delimited Files in a Join

Both files in the join must be in ascending order of the concatenation of join fields. If the files are not in the same sort order, the following message is displayed:
```
(FOC1071)	 VALUE FOR JOIN 'TO' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED
```
Many-to-many joins are not supported when the cross-referenced file is sequential. If multiple host field values match cross-referenced field values, the following message is generated:
```
(FOC1072) DUPLICATES IN JOIN 'FROM' FIELD  : filename/value
```
A comma-delimited file used as the cross-referenced file must consist of a single segment.

Top of page

Example: Joining Two Comma-Delimited Files

The following request creates a comma-delimited file from the ITEMS data source and another comma-delimited file from the VIDEOTRK data source, sorted in the same order. It then joins the two comma-delimited files and issues a report request against the joined files. In order to issue a request against a FORMAT COM data source, the PCOMMA parameter is set ON:

TABLE FILE ITEMS
PRINT PRODNAME OURCOST RETAILPR ON_HAND
BY PRODCODE
ON TABLE HOLD AS ITEMCOM FORMAT COM
END
 
TABLE FILE VIDEOTRK
PRINT LASTNAME FIRSTNAME
BY PRODCODE
WHERE LASTNAME NE 'NON-MEMBER'
ON TABLE HOLD AS VIDCOM FORMAT COM
END
 
JOIN PRODCODE IN VIDCOM TO PRODCODE IN ITEMCOM AS J1
 
SET PCOMMA = ON
 
TABLE FILE VIDCOM
PRINT LASTNAME FIRSTNAME PRODNAME
WHERE PRODNAME LIKE '%SHIRT%'
END

The output is:

LASTNAME         FIRSTNAME   PRODNAME
--------         ---------   --------
WILLIAMS         KENNETH     T-SHIRT RED
WHITE            PATRICIA    T-SHIRT RED
CHANG            ROBERT      T-SHIRT RED
CRUZ             IVY         T-SHIRT BLUE
LEVINE           JOSHUA      T-SHIRT BLUE
CHANG            ROBERT      T-SHIRT BLUE
JOSEPH           JAMES       SWEATSHIRT
CHANG            ROBERT      SWEATSHIRT