This example shows how to perform a difference join for the CUSTOMER and ORDER tables, which retrieves all customers who do not have orders.
To perform this join, you first join the CUSTOMER and ORDER tables through ASF, specifying the CUSTOMER table as source table #1. Then you modify the ASF-generated subschema as shown below.
Screen 1
CA CDEF CA - Automatic System Facility ** Column Definition ** DC560302 ADD/MODIFY COLUMNS Table Name: CUSTOMER _ PF1 - Help _ PF3 - Extended Selection Definition _ PF2 - Extended Column Definition _ PF4 - Extended Key Definition Page: 1 of 1 Unique Display Ext Column Name Width Type Key Seq Screen CUSTNUM 4 NUMERIC X 1 _ CUSTNAME 30 TEXT _ 2 _ STREET 30 TEXT _ 3 _ CITY 30 TEXT _ 4 _ ZIP 9 TEXT _ 5 _ SALESREP 4 NUMERIC _ 6 _
Screen 2
CA CDEF CA - Automatic System Facility ** Column Definition ** DC560302 ADD/MODIFY COLUMNS Table Name: ORDER _ PF1 - Help _ PF3 - Extended Selection Definition _ PF2 - Extended Column Definition _ PF4 - Extended Key Definition Page: 1 of 1 Unique Display Ext Column Name Width Type Key Seq Screen CUSTNUM 4 NUMERIC 1 _ ORDNUM 4 NUMERIC X 2 _ AMOUNT 6 CURRENCY _ 3 _
Screen 3
CA TDEF CA - Automatic System Facility ** Table Definition ** DC560005 MODIFY AND/OR SELECT NEXT ACTIVITY _ PF1 - Help _ PF4 - Extended Table Definition _ PF2 - Define Columns _ PF5 - Delete Table Definition _ PF3 - Generate _ PF6 - Message Screen Table Name.: CHEAP-CUSTOMERS Table Owner: JSS Defn Number: 106 View/Stored: VIEW Status.....: GENERATED Comments...: Table Derivation Source Table #1 Table Name.: CUSTOMER Table Owner: JSS Source Table #2 Table Name.: ORDER Table Owner: JSS Column #1: Column #2 Where Column #1 EQ Column #2
ASF-generated Subschema (Regular Join)
ADD
SUBSCHEMA NAME IS RU000106 OF SCHEMA NAME IS IDMSR VERSION IS 1
.
.
ADD
LOGICAL RECORD NAME IS CHEAP-CUSTOMERS
ELEMENTS ARE
ASF-RDEF-REC
RFUR-000101-OOAK
RFUR-000102-OOAK
RFUR-000101-DATA
RFUR-000102-DATA
.
.
ADD
PATH-GROUP NAME IS OBTAIN CHEAP-CUSTOMERS
.
.
.
FIND FIRST ASF-RDEF-OWN WITHIN IDMSR-AREA
ON 0000 NEXT
ON 0307 ITERATE
OBTAIN FIRST ASF-RDEF-REC WITHIN RDEFSET
WHERE SORTKEY EQ 000000101
ON 0326 RETURN DEFN-MISSING
ON 0000 NEXT
OBTAIN RFUR-000101-OOAK
WHERE DBKEY EQ RDEF-OWNER-DBKEY OF ASF-RDEF-REC OF LR
ON 0326 RETURN OOAK-MISSING
ON 0000 NEXT
EVALUATE RDEF-TIME-STAMP
OF ASF-RDEF-REC OF LR LE OWNER-TIME-STAMP
OF RFUR-000101-OOAK OF LR
ON 2001 RETURN SYNC-ERROR
ON 0000 NEXT
OBTAIN FIRST ASF-RDEF-REC WITHIN RDEFSET
WHERE SORTKEY EQ 000000102
ON 0326 RETURN DEFN-MISSING
ON 0000 NEXT
OBTAIN RFUR-000102-OOAK
WHERE DBKEY EQ RDEF-OWNER-DBKEY OF ASF-RDEF-REC OF LR
ON 0326 RETURN OOAK-MISSING
ON 0000 NEXT
EVALUATE RDEF-TIME-STAMP OF ASF-RDEF-REC
OF LR LE OWNER-TIME-STAMP
OF RFUR-000102-OOAK OF LR
ON 2001 RETURN SYNC-ERROR
ON 0000 NEXT
OBTAIN EACH RFUR-000101-DATA USING INDEX
ON 0326 ITERATE
ON 0000 NEXT
OBTAIN EACH RFUR-000102-DATA USING RFFS-000102-0001
WHERE CUSTNUM OF RFUR-000102-DATA EQ CUSTNUM OF
RFUR-000101-DATA OF LR
ON 0326 ITERATE ◄-------------- Path iterates when a
ON 0000 NEXT match on CUSTNUM is not found.
Modified Subschema (Difference Join)
ADD
SUBSCHEMA NAME IS RU000106 OF SCHEMA NAME IS IDMSR VERSION IS 1
.
.
ADD
LOGICAL RECORD NAME IS CHEAP-CUSTOMERS
ELEMENTS ARE
ASF-RDEF-REC
RFUR-000101-OOAK
RFUR-000102-OOAK
RFUR-000101-DATA
RFUR-000102-DATA
.
ADD
PATH-GROUP NAME IS OBTAIN CHEAP-CUSTOMERS
.
.
FIND FIRST ASF-RDEF-OWN WITHIN IDMSR-AREA
ON 0000 NEXT
ON 0307 ITERATE
OBTAIN FIRST ASF-RDEF-REC WITHIN RDEFSET
WHERE SORTKEY EQ 000000101
ON 0326 RETURN DEFN-MISSING
ON 0000 NEXT
OBTAIN RFUR-000101-OOAK
WHERE DBKEY EQ RDEF-OWNER-DBKEY OF ASF-RDEF-REC OF LR
ON 0326 RETURN OOAK-MISSING
ON 0000 NEXT
EVALUATE RDEF-TIME-STAMP OF ASF-RDEF-REC
OF LR LE OWNER-TIME-STAMP
OF RFUR-000101-OOAK OF LR
ON 2001 RETURN SYNC-ERROR
ON 0000 NEXT
OBTAIN FIRST ASF-RDEF-REC WITHIN RDEFSET
WHERE SORTKEY EQ 000000102
ON 0326 RETURN DEFN-MISSING
ON 0000 NEXT
OBTAIN RFUR-000102-OOAK
WHERE DBKEY EQ RDEF-OWNER-DBKEY OF ASF-RDEF-REC OF LR
ON 0326 RETURN OOAK-MISSING
ON 0000 NEXT
EVALUATE RDEF-TIME-STAMP OF ASF-RDEF-REC
OF LR LE OWNER-TIME-STAMP
OF RFUR-000102-OOAK OF LR
ON 2001 RETURN SYNC-ERROR
ON 0000 NEXT
OBTAIN EACH RFUR-000101-DATA USING INDEX
ON 0326 ITERATE
ON 0000 NEXT
OBTAIN EACH RFUR-000102-DATA USING RFFS-000102-0001
WHERE CUSTNUM OF RFUR-000102-DATA EQ CUSTNUM OF
RFUR-000101-DATA OF LR
┌----------------------------┐
│ ON 0326 NEXT │
│ ON 0000 ITERATE │ ◄------------------------- Path iterates when a
│ . │ match on CUSTNUM is found
└----------------------------┘
|
Copyright © 2014 CA.
All rights reserved.
|
|