This example shows how to calculate summary information for the CUSTOMER and ORDER tables. This information includes a count of the orders for each customer and a total of each customer's order amounts.
To calculate summary information, you can first join the tables through ASF. When you join the tables, you should add two IDD-defined work fields (one will contain the order total and the other will contain a count of orders). You should also add a calculation for each field. The COLUMN(AMOUNT) calculation provides a good start.
Screen 1
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.: CUST-ORD-SUMMARY Table Owner: JSS Defn Number: 105 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
Screen 2
CA XCOL CA - Automatic System Facility *Extended Column Definition* DC560407 MODIFY COLUMN DEFINITION Table Name: CUST-ORD-SUMMARY _ PF1 - Help _ PF2 - Next Column _ PF3 - Message Screen Displayed Column Name Stored Column Name CUST-TOTAL CUST-TOTAL Display Format Stored Format -Z(16).9(02) S9(16)V9(02) Usage: 3 D/0/1/2/3 Display/Comp/Comp-1/Comp-2/Comp-3 Comments.....: Column Level: WORK Initial Value: Calculation..: COLUMN(AMOUNT) Edit Table: Version: Code Table: Version: U U/P Unprotected/Protected N B/R/P/T/G/Y/W/N Blue/Red/Pink/Turquoise Green/Yellow/White/Nocolor N Y/N Blank When Zero N N/R Normal/Reverse Video D B/D/K Bright/Display/Dark N Y/N Blink/Noblink
Screen 3
CA XCOL CA - Automatic System Facility *Extended Column Definition* DC560407 MODIFY COLUMN DEFINITION Table Name: CUST-ORD-SUMMARY _ PF1 - Help _ PF2 - Next Column _ PF3 - Message Screen Displayed Column Name Stored Column Name ORDER-COUNT ORDER-COUNT Display Format Stored Format -Z(16).9(02) S9(16)V9(02) Usage: 3 D/0/1/2/3 Display/Comp/Comp-1/Comp-2/Comp-3 Comments.....: Column Level: WORK Initial Value: Calculation..: COLUMN(AMOUNT) Edit Table: Version: Code Table: Version: U U/P Unprotected/Protected N B/R/P/T/G/Y/W/N Blue/Red/Pink/Turquoise Green/Yellow/White/Nocolor N Y/N Blank When Zero N N/R Normal/Reverse Video D B/D/K Bright/Display/Dark N Y/N Blink/Noblink
ASF-defined Subschema
ADD
SUBSCHEMA NAME IS RU000105 OF SCHEMA NAME IS IDMSR VERSION IS 1
.
.
.
ADD
LOGICAL RECORD NAME IS CUST-ORD-SUMMARY
ELEMENTS ARE
ASF-RDEF-REC
RFUR-000101-OOAK
RFUR-000102-OOAK
RFUR-000101-DATA
RFUR-000102-DATA
RFUR-000105-WORK VERSION 1
.
ADD
PATH-GROUP NAME IS OBTAIN CUST-ORD-SUMMARY
.
.
.
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
ON 0000 NEXT
COMPUTE CUST-TOTAL OF RFUR-000105-WORK OF LR = AMOUNT OF ◄--┐
RFUR-000102-DATA OF LR ├--- Work field
ON 0000 NEXT │ calculations
COMPUTE ORDER-COUNT OF RFUR-000105-WORK OF LR = AMOUNT OF ◄--┘
RFUR-000102-DATA OF LR
ON 0000 NEXT
.
Modified Subschema
ADD
SUBSCHEMA NAME IS RU000105 OF SCHEMA NAME IS IDMSR VERSION IS 1
.
.
.
ADD
LOGICAL RECORD NAME IS CUST-ORD-SUMMARY
ELEMENTS ARE
ASF-RDEF-REC
RFUR-000101-OOAK
RFUR-000102-OOAK
RFUR-000101-DATA
RFUR-000102-DATA
RFUR-000105-WORK VERSION 1
.
ADD
PATH-GROUP NAME IS OBTAIN CUST-ORD-SUMMARY
.
.
.
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 DO │
│ COMPUTE CUST-TOTAL OF RFUR-000105-WORK OF LR = 0 ◄--┐ │
│ ON 0000 NEXT ├-- Initializes
│ COMPUTE ORDER-COUNT OF RFUR-000105-WORK OF LR = 0 ◄--┘ the work fields
│ ON 0000 NEXT │
│ END │
│ 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 RETURN LR-FOUND ◄----------------------- Returns order amounts
│ ON 0000 DO and counts per customer
│ COMPUTE CUST-TOTAL OF RFUR-000105-WORK OF LR = CUST-TOTAL OF ◄-- Totals the
│ RFUR-000105-WORK OF LR + AMOUNT OF RFUR-000102-DATA OF LR order amounts
│ ON 0000 NEXT per customer
│ COMPUTE ORDER-COUNT OF RFUR-000105-WORK OF LR = ORDER-COUNT OF ◄-- Counts the
│ RFUR-000105-WORK OF LR + 1 │ number of
│ ON 0000 ITERATE ◄-------┐ │ orders per
│ END │ │ customer
│ . └-- Retrieves another ORDER record │
└------------------------------------------------------------------┘
|
Copyright © 2014 CA.
All rights reserved.
|
|