Predetermined Join Column Concepts

A Predetermined Join Column, or PJC, is a special feature of the LANSA Repository. A PJC is a special kind of virtual column as described in Virtual Column Concepts. Unlike the other types of Repository virtual tables, a PJC is a virtual column whose value is determined by a value or values in another table. (Other virtual columns are always based on columns within its own table definition.) PJCs use access routes to determine the table relationships.

A Predetermined Join Column is used to hold the result of various operations performed on information retrieved from the table nominated by the access route. The column must be defined in the LANSA Repository.

The type of operations available to be performed depend on the relationship between the tables as indicated by the number of records expected on the access route definition.

When the relationship is 1:many (refer to access route Maximum Records), the operations are TOTAL, MAXIMUM, MINIMUM, AVERAGE, and COUNT. (Also refer to PJC Type in the Technical Reference Guide.)

When the relationship is 1:1 using the key defined on the access route, a single value LOOKUP will be retrieved into the Predetermined Join Column. For example, a product description can be retrieved using the product code if an access route has been defined between an order line table and a product table. A Keep Last value can be nominated to reduce I/Os.

When the relationship is 1:many (number of records retrieved is greater than 1) the Predetermined Join Column will hold the result of the selected operation on the selected column in the table 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 table could be retrieved for display with the order header table.

The access route on which Predetermined Join Columns are defined includes in its definition two attributes which affect Predetermined Join Columns. They are the Keep Last value and the Derivation. Both of these attributes apply to all Predetermined Join Columns defined on the access route.

The Keep Last value only affects Predetermined Join Columns when the access route relationship is 1:1. It causes the last "nnn" values retrieved from the accessed table to be stored in memory and thereby reducing I/Os.

The Derivation attribute nominates when the Predetermined Join Columns are to be retrieved from the accessed table, i.e. either before or after virtual columns which are derived 'After input from table'.

Predetermined join columns will be displayed when prompting for columns in the RDML editor in the same way as virtual columns.

Predetermined join columns are dependent on the access route they are defined on.


Also See

PJC Examples

PJC Considerations