Predetermined Join Field Examples
Example 1
To retrieve a customer description to be displayed when processing an order:
- First an access route is defined from the order to the customer file. The maximum records retrieved is defined as 1 and the customer code field from the order file as the key.
- Next option 23 is taken against the access route and the fields in the customer file are displayed. The customer description field is selected and in the window displayed after using F21 the Predetermined Join Field to receive the description can be nominated against the LOOKUP operation. The source field name can be used and if required a KEEP LAST value entered.
When the I/O module for the order file is made operational the Predetermined Join Field customer description will be available in the same way as a virtual field when coding RDML without having to include any type of fetch from the customer file.
Where a code file is repeatedly accessed for the same information an appropriate KEEP LAST value will reduce the number of I/Os performed.
Example 2
To retrieve the descriptions of the 'from' and 'to' warehouses in a stock transfer file:
- 2 access routes must be defined, as in example 1, from the transfer file to the warehouse file. The first will use the 'from' warehouse code field as the key and the second will use the 'to' warehouse code field as key.
- When defining the Predetermined Join Field on the first access route the source field name can be used but when defining the Predetermined Join Field on the second access route a different field name must be used so that both the 'from' and 'to' descriptions are available at the same time.
Example 3
To retrieve the total value and number of lines in an order when displaying the order header details:
- Define an access route from the order header file to the order line file with the appropriate key. The number of records expected will be the maximum number of lines in an order.
- Select the access route for Predetermined Join Field definition.
- From the displayed fields in the order line file select the line value field. In the displayed window enter the Predetermined Join Field name against the TOTAL operation. The count of order lines can also be performed on this field or on any other field in the file.
When the order file is made operational the total value and line count fields are available in the RDML editor along with the order fields without coding any reference to the order line file.
Example 4
To calculate a price for each line of an order using a Predetermined Join Field and a virtual field:
- Define an access route from the order line file to the product file with a one to one relationship (maximum expected records - 1), a keep last value if appropriate, set "PJF before/after virtual fields derived" to before (B) and use the product code field from the order line file as the key.
- Define a virtual field of extended definition type "mathematical calculation" to be derived "after input from file". Define the calculation as follows: product quantity (from order line file) multiplied by product price (Predetermined Join Field from product file) equals virtual field.
By including the product price (Predetermined Join Field) and product value (virtual field) when defining a select command in RDML, the arithmetic will be performed without any additional RDML code.
Comments/Warnings
Although the Predetermined Join Field processing is coded within the I/O module the source files are only accessed when one of the Predetermined Join Fields on a particular access route is used in the function calling the I/O module.
This means that, unless the Predetermined Join Field is explicitly specified in the I/O operation (for example FETCH or SELECT), it would not be retrieved and calculated from the source file (not even when it is derived from a Virtual Field).
If the function has been DBOPTIMIZED the Predetermined Join Field processing is included in the I/O module logic which is embedded in the function.
If the file to be accessed by the access route is defined as a high speed table the source information for the Predetermined Join Fields will be retrieved from the high speed table thereby reducing I/Os.
Warning: Read the appropriate sections on high speed tables before attempting to use them.
When defining Predetermined Join Fields careful thought should be given to their use. They are very efficient for looking up code files for descriptions when used with a KEEP LAST value or accessing a high speed table, but if they are used excessively each file accessed will affect the number of files used in the I/O module and DBOPTIMIZSED functions (see for a further explanation). Also they could greatly increase the number of I/Os performed by the I/O module where there are 1 to many relationships.
As a guide, do not access more than 10 to 15 files.