These examples apply to defining an access route within a table definition.
Four related table definitions are used in these examples. These are defined as follows:
Table: | ORDHDR | ORDLIN | CUSMST | PROMST | |
|---|---|---|---|---|---|
Desc: | Order header | Order lines | Customer master | Product master | |
Columns: | ORDNUM CUSTNO DATE | ORDNUM ORDLIN QUANTY PRODNO | CUSTNO NAME ADDR1 ADDR2 ADDR3 | PRODNO DESC PRICE TAXRTE | |
Primary Key | 1 | ORDNUM | ORDNUM | CUSTNO | PRODNO |
2 | ORDLIN | ||||
Indexes: | ORDHDRV1O | RDLINV1 | |||
by key | 1 | CUSTNO | PRODNO |
To allow complete traversal of this database by users the following access routes would be defined:
Definition Name | Access Route Name | Table to be accessed via this route | Max Rcds Expected | Action to be taken if no records found | Key Columns/Values Used for access |
|---|---|---|---|---|---|
ORDHDR | ORDHRT1 | ORDLIN | 99 | IGNORE | ORDNUM |
ORDHRT2 | CUSMST | 1 | ABORT | CUSTNO | |
ORDLIN | ORDLRT1 | ORDHDR | 1 | ABORT | ORDNUM |
ORDLRT2 | PROMST | 1 | ABORT | PRODNO | |
CUSMST | CUSMRT1 | ORDHDRV1 | 9999 | IGNORE | CUSTNO |
PROMST | PROMRT1 | ORDLINV1 | 9999 | IGNORE | PRODNO |
From this table it is easy to see how LANSA can follow the access route chains. For example, if the user nominated PROMST (product master) as a "starting" or "base" table, then the following would occur:
- Using access route PROMRT1 a route to ORDLIN (order lines) is available (via index ORDLINV1) which will access all invoice lines that use the product.
- From ORDLIN the associated order header (ORDHDR) details can be picked up using access route ORDLRT1. The access route ORDLRT2 back to PROMST would be ignored as it forms a "loop".
- From ORDHDR the associated customer details (CUSMST) can be found via access route ORDHRT2. Again ORDHRT1 would be ignored as it forms a "loop".