3.14.1 What Is a Predetermined Join Field?
A Predetermined Join Field is used to hold the result of various operations performed on information retrieved from the file nominated by the access route. It must be defined in the Data Dictionary.
The type of operations available to be performed depend on the relationship between the files as indicated by the number of records expected on the access route definition.
The operations are TOTAL, MAXIMUM, MINIMUM, AVERAGE, COUNT, and LOOKUP.
When the relationship is 1 to 1, (number of records 1) using the key defined on the access route a single value will be retrieved into the Predetermined Join Field. For example a product description can be retrieved using the product code if an access route has been defined between an order line file and a product file. A "keep last" value can be nominated to reduce I/Os.
When the relationship is 1 to many (number of records greater than 1) the Predetermined Join Field will hold the result of the selected operation on the selected field in the file nominated by the access route, retrieved using the key in the access route definition. For example the total of the line values from an order line file could be retrieved for display with the order header file.
The access route on which Predetermined Join Fields are defined includes in its definition two attributes which affect Predetermined Join Fields. They are the keep last value and the retrieval timing attribute. Both of these attributes apply to all Predetermined Join Fields defined on the access route.
The keep last value only affects Predetermined Join Fields when the access route relationship is one to one. It causes the last "nnn" values retrieved from the accessed file to be stored in memory and thereby reducing I/Os.
The retrieval timing attribute nominates when the Predetermined Join Fields are to be retrieved from the accessed file, i.e. either before or after virtual fields which are derived 'After input from file'.
Predetermined join fields will be displayed when prompting for fields in the RDML editor in the same way as virtual fields.
If the file to be accessed is a high speed table, the high speed table will be used thus reducing I/Os.
Predetermined join fields are dependent on the access route they are defined on.
WARNING: Predetermined join fields are very efficient when used to look up descriptions in code files where the file accessed is a high speed table but overuse of this facility could cause the opposite effect, particularly in DBOPTIMISED functions when the accessed file is not a high speed table. As a guide, do not access more than 10 to 15 files for Predetermined Join Fields.