Purpose
A repeating relationship occurs when the name of a table is repeated as the subject of a relationship. In general, a repeating relationship should be used when the subject of the relationship wanted is not the object of the last relationship specified. The relationships established can be a combination of complex and simple joins. For example, A is related to B and A is related to C; therefore, A contains two different keys or columns which are common to either B or C.
Example
The following example shows how TABLE-B can be related to TABLE-C through their relationship to TABLE-A.
Repeating Relationship Illustration
Example
The following FIND statement uses the common key CUST-ID-KEY to connect the CAI-CUST-REC table with the CAI-DETAIL-REC table and the CAI-ORDERS-REC table in a repeating relationship.
FIND 10 CAI-CUST-REC ROWS
RELATED BY CUST-ID-KEY TO
CAI-DETAIL-REC ROWS
AND CAI-CUST-REC RELATED BY CUST-ID-KEY
TO CAI-ORDERS-REC ROWS
WITH ORD-YR = '87'
Explanation
The preceding example contains two RELATED BY statements. This repetition indicates that a relationship must exist between more than two tables before a logical row is selected. In the example, the relationship of three tables is established.
In the preceding sample FIND statement, a relationship must exist between CAI-CUST-REC and CAI-DETAIL-REC tables before a row is chosen. This relationship is established through the common key, CUST-ID-KEY.
For example, if a CAI-CUST-REC row, a CAI-DETAIL-REC row and a CAI-ORDERS-REC row all exist in their respective table types with a CUST-ID-KEY of 01009, all the relationships qualify. When edited and executed, the preceding query statement can yield information from each of the named tables. However, the relationship between CAI-DETAIL-REC rows and CAI-ORDERS-REC rows in the example preceding does not need to specify the same key name as specified in the first relationship. The second relationship can specify an entirely different key.
Example
The following sample illustrates a repeating relationship.
FIND 10 CAI-DETAIL-REC ROWS
RELATED BY ITEM-ID-KEY TO CAI-ITEMS-REC ROWS AND
CAI-DETAIL-REC ROWS RELATED BY ORD-ID-KEY TO CAI-ACCTS-REC ROWS
WITH ORD-AMT = 0
Explanation
In the preceding example, CAI-DETAIL-REC and CAI-ITEMS-REC tables are related by their common key ITEM-ID-KEY while CAI-DETAIL-REC and CAI-ACCTS-REC tables are related by their common key ORD-ID-KEY.
In a repeating relationship, the first table is repeatedly joined, or related, to each succeeding table. Each table is linked with the first table based on the value in a common column or key. However, the same column or key need not be used to link the first table with all the others.
Example
The following DQL statements show a repeating relationship of four tables:
FIND ALL CUSTOMER ROWS
RELATED BY CUST-ID TO ACCOUNTS ROWS
AND CUSTOMER ROWS RELATED BY CUST-ID TO ORDERS ROWS
AND CUSTOMER ROWS RELATED BY VENDOR-NO TO VENDORS ROWS
|
Copyright © 2014 CA.
All rights reserved.
|
|