Deriving New Fields from Existing Fields

Fields in a logical file can be derived from fields in the physical file the logical file is based on or from fields in the same logical file. For example, you can concatenate, using the CONCAT keyword, two or more fields from a physical file to make them appear as one field in the logical file. Likewise, you can divide one field in the physical file to make it appear as multiple fields in the logical file with the SST keyword.

Concatenated Fields

Using the CONCAT keyword, you can combine two or more fields from a physical file record format to make one field in a logical file record format. For example, a physical file record format contains the fields Month, Day, and Year. For a logical file, you concatenate these fields into one field, Date.

The field length for the resulting concatenated field is the sum of the lengths of the included fields (unless the fields in the physical file are binary or packed decimal, in which case they are changed to zoned decimal). The field length of the resulting field is automatically calculated by the system. A concatenated field can have:

• Column headings
• Validity checking
• Text description
• Edit code or edit word (numeric concatenated fields only)

When fields are concatenated, the data types can change (the resulting data type is automatically determined by the system). The following rules and restrictions apply:

• The OS/400 program assigns the data type based on the data types of the fields that are being concatenated.
• The maximum length of a concatenated field varies depending on the data type of the concatenated field and the length of the fields being concatenated. If the concatenated field is zoned decimal (S), its total length cannot exceed 31 bytes; if it is character (A), its total length cannot exceed 32 766 bytes.
• In join logical files, the fields to be concatenated must be from the same physical file. The first field specified on the CONCAT keyword identifies which physical file is to be used. The first field must, therefore, be unique among the physical files on which the logical file is based, or you must also specify the JREF keyword to specify which physical file to use.
• The use of a concatenated field must be I (input only) if the concatenated field is variable length. Otherwise, the use may be B (both input and output).
• REFSHIFT cannot be specified on a concatenated field that has been assigned a data type of O or J.
• If any of the fields contain the null value, the result of concatenation is the null value.

Notes:

1. Numeric fields with decimal precision other than zero cannot be included in a concatenated field.

2. Date, time, timestamp, and floating-point fields cannot be included in a concatenated field.

The following shows the field description in DDS for concatenation. (The CONCAT keyword is used to specify the fields to concatenate.)

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 A 00101A MONTH 00102A DAY 00103A YEAR 00104A DATE CONCAT(MONTH DAY YEAR) A

In this example, the logical file record format includes the separate fields of Month, Day, and Year, as well as the concatenated Date field. Any of the following can be used:

• A format with the separate fields of Month, Day, and Year
• A format with only the concatenated Date field
• A format with the separate fields Month, Day, Year and the concatenated Date field

When both separate and concatenated fields exist in the format, any updates to the fields are processed in the sequence in which the DDS is specified. In the previous example, if the Date field contained 103188 and the Month field is changed to 12, when the record is updated, the month in the Date field would be used. The updated record would contain 103188. If the Date field were specified first, the updated record would contain 123188.

Concatenated fields can also be used as key fields and select/omit fields.

Substring Fields

You can use the SST keyword to specify which fields (character, hexadecimal, or zoned decimal) are in a substring. (You can also use substring with a packed field in a physical file by specifying S (zoned decimal) as the data type in the logical file.) For example, assume you defined the Date field in physical file PF1 as 6 characters in length. You can describe the logical file with three fields, each 2 characters in length. You can use the SST keyword to define MM as 2 characters starting in position 1 of the Date field, DD as 2 characters starting in position 3 of the Date field, and YY as 2 characters starting in position 5 of the Date field.

The following shows the field descriptions in DDS for these substring fields. The SST keyword is used to specify the field to substring.

|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8 A R REC1 PFILE(PF1) A A MM I SST(DATE 1 2) A DD I SST(DATE 3 2) A YY I SST(DATE 5 2) A

Note that the starting position of the substring is specified according to its position in the field being operated on (Date), not according to its position in the file. The I in the Usage column indicates input-only.

Substring fields can also be used as key fields and select/omit fields.

Renamed Fields

You can name a field in a logical file differently than in a physical file using the RENAME keyword. You might want to rename a field in a logical file because the program was written using a different field name or because the original field name does not conform to the naming restrictions of the high-level language you are using.

Translated Fields

You can specify a translation table for a field using the TRNTBL keyword. When you read a logical file record and a translation table was specified for one or more fields in the logical file, the system translates the data from the field value in the physical file to the value determined by the translation table.