Purpose
You can have CA Dataquery perform a calculation at control breaks and print the result on the report, along with a legend.
Syntax Diagram
The syntax for specifying a mathematical function at a control break is:
►►─ WHEN ─ table-name ─┬────────────┬─ named-control-break ───────────────────► └─ (status) ─┘ ►─┬────────────┬─ DO ────────────────────────────────────────────────────────► ├─ BREAKS ───┤ └─ FINISHED ─┘ ►─┬─ PAGE-BREAK ─┤ more-choices ├ ─────────────────────┬─────────────────────► └─┬───────────────────┬─ function ─┤ more-choices ├ ─┘ └─ 'report-legend' ─┘ ►─┬──────────────────────────┬───────────────────────────────────────────────►◄ └─ PICture 'edit-pattern' ─┘
Expansion of more-choices
├──┬─ 'table-name ─┬────────────┬─ 'column-name ─┬─────────────────────────────┤ │ └─ (status) ─┘ │ └─ result-name ───────────────────────────────┘
Repeat WHEN statement once for each function or page break wanted. Multiple DO statements may be used with one WHEN statement.
Explanation
The following explains the previous syntax diagram for WHEN/DO statements.
Refers to the specific table which contains the designated column or key which follows. It is not needed if it is the same as the most recently named table in the query.
(Optional) If authorized, use to specify a Datadictionary definition of a table that is being tested by programmers responsible for its maintenance. If the FIND or COUNT statement specifies a TEST status for a table, all subsequent statements in the same query reference the same status and version of the table, unless a different status is specified. The following describes each status you can specify.
Note: The status must be enclosed in parentheses, and there must be no spaces between the table name and the left parenthesis.
Finds the named table in the first test version that meets the specifications.
(Where nnn refers to the version number of the test status you want.) Finds the named table in the specified test version.
PRODuction is the default status if not specified.
Specifies the name of a column or key which was designated as a control-break in a SORT statement.
Used only for readability.
Specifies a legend to be printed beside the results of the function calculation. Can be up to 64 characters in length. It can contain a heading substitution string which will result in the value of the column being substituted in the legend at the indicated place when it is printed. It must be enclosed in single apostrophes. If no report legend is given, the default will be the function followed by the name of the function column.
Note: Select the System Profile Help Topic from the List of Help Topics panel (HELP command) to determine what heading substitution string is used at your site. && is the default provided by CA.
Specifies the function to be performed. The following chart describes each function you can perform and the entry for your query:
|
Function |
Entry |
At Designated Control Break: |
|---|---|---|
|
Average |
AVG |
Averages the values for the specified numeric column.
|
|
Count |
CNT |
Counts the number of occurrences of the specified column.
|
|
Maximum |
MAX |
Finds the maximum value for the specified numeric column.
|
|
Minimum |
MIN |
Finds the minimum value for the specified numeric column.
|
|
Sum |
SUM |
Sums the values for the specified numeric column. |
Specifies that CA Dataquery should start a new page or a new screen when the value of the control break changes. (WHEN/DO PAGE-BREAK does not operate with totals-only.)
Specifies the name of the column on which to perform the specified function. The named column must be defined in Datadictionary as a valid numeric type, which includes floating-point types.
Also, a column can be one occurrence within a repeating field. If that is the case, use subscripts to identify which occurrence is to be used. A typical subscript looks like this:
SALES-RESULTS (5,2)
(See the CA Dataquery User Guide for more information.)
Specifies the name of the temporary result on which to perform the specified function.
(Optional) Specifies that the data should be formatted according to the edit pattern specified next.
A series of characters that specify how you want the data to be formatted. See PICTURE Clause.
Sample
The following query illustrates a mathematical function performed at a control break.
FIND 10 CAI-SLSHST-REC ROWS
WITH SHIP-QTY > 0
RELATED BY SLMN-ID TO CAI-ORDERS-REC
SET NET-COST (8.2) = CAI-SLSHST-REC SHIP-QTY * UNIT-PRICE
SET DISCOUNT (5.2) = NET-COST * DISC-PCT
SORT CAI-SLSHST-REC BY (ORD-ID) AND CAI-ORDERS-REC SHIP-ID
PRINT FROM CAI-ORDERS-REC ORD-ID
SHIP-ID
FROM CAI-SLSHST-REC ITM-ID
SHIP-QTY
UNIT-PRICE
(NET-COST)
DISCOUNT
WHEN CAI-SLSHST-REC ORD-ID BREAKS
DO MAX CAI-SLSHST-REC UNIT-PRICE
Output
The following sample output is generated by the preceding query.
=> 01/02/2010 CA Dataquery PAGE 1 17:21:16 DETAIL ORD-ID SHIP-ID ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT ------ ------- ---------- -------- ---------- ------------- --------- 08811 00001 O50006 0000203 00014.75 00002994.25 00010.00 00002 O50006 0000010 00014.75 00000147.50 00002.75 TOTAL ORD-ID 08811 * 00003141.75 MAXIMUM UNIT-PRICE 14.75 09312 00012 O50006 0000100 00014.75 00001475.00 00010.00 00015 O90001 0000010 00090.00 00000900.00 00002.75 TOTAL ORD-ID 08811 * 00001375.00 MAXIMUM UNIT-PRICE 90.00 * GRAND TOTAL * 00079923.76 -------------------------------- LAST PAGE --------------------------------- <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
|
Copyright © 2014 CA.
All rights reserved.
|
|