Example 3: Dynamically joining database files

This example shows how to use mapped fields as join fields.

You can use fields defined on the MAPFLD parameter for either one of the join field pairs. This is useful when the key in the secondary file is defined as a single field (for example, a 6-character date field) and there are separate fields for the same information (for example, month, day, and year) in the primary file. Assume that FILEA has character fields Year, Month, and Day and needs to be joined to FILEB which has the Date field in YYMMDD format. Assume that you have defined file JOINAB with the required format. You can specify:

OVRDBF     FILE(JOINAB) TOFILE(FILEA) SHARE(*YES)
OPNQRYF    FILE(FILEA FILEB) FORMAT(JOINAB) +
              JFLD((YYMMDD FILEB/DATE)) +
              MAPFLD((YYMMDD 'YEAR *CAT MONTH *CAT DAY'))
CALL       PGM(PGME) /* Created using file JOINAB as input */
CLOF       OPNID(FILEA)
DLTOVR     FILE(JOINAB)

The MAPFLD parameter defines the YYMMDD field as the concatenation of several fields from FILEA. You do not need to specify field attributes (for example, length or type) for the YYMMDD field on the MAPFLD parameter because the system calculates the attributes from the expression.