PJC Examples

Example 1

Retrieve a customer description to be displayed when processing an order:

• First an access route is defined from the order table to the customer table. The maximum records retrieved is defined as 1 and the customer code column from the order table is used as the key.

• Next a Predetermined Join Column is added to receive the customer description using the LOOKUP operation. The source column name can be used and, if required, a KEEP LAST value entered.

When the I/O module for the order table is made operational the Predetermined Join Column customer description will be available in the same way as a virtual column when coding RDML without having to include any type of fetch from the customer table.

Where a code table is repeatedly accessed for the same information an appropriate KEEP LAST value will reduce the number of I/Os performed.

Example 2

Retrieve the descriptions of the 'from' and 'to' warehouses in a stock transfer table:

• Two access routes must be defined, as in Example 1, from the transfer table to the warehouse table. The first will use the 'from' warehouse code column as the key and the second will use the 'to' warehouse code column as key.

• When defining the Predetermined Join Column on the first access route, the source column name can be used; but when defining the Predetermined Join Column on the second access route, a different column name must be used so that both the 'from' and 'to' descriptions are available at the same time.

Example 3

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 table to the order line table 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 Column definition.

• From the displayed columns in the order line table, select the line value column. Add the Predetermined Join Column using the TOTAL operation. The count of order lines can also be performed on this column or on any other column in the table.

When the order table is made operational, the total value and line count columns are available in the RDML editor along with the order columns without coding any reference to the order line table.

Example 4

Calculate a price for each line of an order using a Predetermined Join Column and a virtual column:

• Define an access route from the order line table to the product table with a 1:1 relationship (by settimg the Maximum Records value), a Keep Last value if appropriate, set the Derivation  to "before virtual columns" and use the product code column from the order line table as the key.

• Define a virtual column of extended definition type "mathematical calculation" to be derived "after input from table". Define the calculation as follows: product quantity (from order line table) multiplied by product price (Predetermined Join Column from product table) equals virtual column.

By including the product price (Predetermined Join Column) and product value (virtual column) when defining a select command in RDML, the arithmetic will be performed without any additional RDML code.

Example 5

Manipulate the Department and Section tables which are normally part of a Personnel System database.

Imagine that you want to be able to make the Department Description column in the Department table part of the Section table. You also want to derive the Section Count column by counting the sections for a specific department in Section table. PJCs will be created to make these two columns part of the Department table.

First, two new virtual columns are created in the Repository:

• Description of Department

• Section Count

Based on the access routes between the Section table and the Department table, a PJC is added to the Section table so that the Description of Department column is a table lookup to the Department Description in the Department table. Description of Department is a virtual column. When it is used by a function, the Repository will actually access the Department table to get the required data since it does not really exist in the Section table. The access route is used to determine how to access the data.

Based on the access routes between the Department table and the Section table, a PJC is added to the Department table so that the Section Count column is calculated by counting the appropriate sections in the Section table. Section Count is a virtual column. When it is used by a function, the Repository will actually access the Section table to get and total the data.

Also See

Predetermined Join Column Concepts