The following example shows how selection criteria works with a RELATED BY statement that uses a common key.
Query
The following sample query relates two tables, CAI-CUST-REC and CAI-ACCTS-REC using the common key CUST-ID-KEY and selecting only rows where ORD-AMT is greater than or equal to 2000.
FIND ALL CAI-CUST-REC ROWS
RELATED BY CUST-ID-KEY TO CAI-ACCTS-REC ROWS
WITH ORD-AMT GTE 2000.00
PRINT FROM CAI-CUST-REC CUST-ID-KEY
NAME
PHONE
CRED-IND
FROM CAI-ACCTS-REC ORD-ID
ORD-AMT
FRT-AMT
PAYMT-AMT
Explanation
CA Dataquery joins rows from the two tables whenever the value in the common key, CUST-ID-KEY, matched. Now that the two tables are related, we can access information for customers from both tables with one query.
Joining the two tables creates a new temporary table during query execution. Only rows containing the specified selection criteria were selected. This temporary table is called the active found set.
Output
Following is the first page of output from the preceding illustrated query and active found set.
=> 01/02/2010 CA Dataquery PAGE 1A 14:15:01 DETAIL CUST-ID-KEY NAME PHONE CRED-IND ORD-ID ----------- ------------------------------ ---------- -------- ------ A0030 CANNON TOOLS CO 4045553322 A 01011 A0030 CANNON TOOLS CO 4045553322 A 01223 G0090 INTERNATIONAL BANK CORP. 2125559889 G 05999 -------------------------------- MORE .... --------------------------------- => <PF1> HELP <PF2> RETURN <PF3> TOTALS ONLY <PF4> DETAIL <PF5> NO TOTALS <PF6> STATS <PF7> BACKWARD <PF8> FORWARD <PF9> NOT USED <PF10> NOT USED <PF11> LEFT <PF12> RIGHT
Scrolling right with PF12 displays page 1B containing the remaining columns requested by the sample query.
=> 01/02/2010 CA Dataquery PAGE 1B 14:15:01 DETAIL ORD-AMT FRT-AMT PAYMT-AMT ----------- ----------- --------- 0004250.00 0000425.00 04547.50 0032950.50 0003295.00 34597.97 0002000.00 0000200.00 00000.00 <= ----------------------------- MORE .... ------------------------------------ <PF1> HELP <PF2> RETURN <PF3> TOTALS ONLY <PF4> DETAIL <PF5> NO TOTALS <PF6> STATS <PF7> BACKWARD <PF8> FORWARD <PF9> NOT USED <PF10> NOT USED <PF11> LEFT <PF12> RIGHT
In the previous figure, the Cannon Tool Company has two entries in the CAI-ACCTS-REC table. In the active found set, there are also two separate rows for Cannon Tool Company.
|
Copyright © 2014 CA.
All rights reserved.
|
|