In this step, you see how to perform mathematical functions on numeric columns, such as finding an average, maximum value or a count of occurrences. If you need a specific mathematical function which CA Dataquery does not provide as a standard function, site management can create a user-defined function (UDF) to meet your request.
Concepts
These are the major concepts related to this step:
WHEN
Tells CA Dataquery that information after this verb describes when to perform a mathematical function. The DO portion of this statement describes the function to be performed. You can use multiple WHEN/DO statements in your query to get the results you need for your report. You can also specify multiple DO statements for a single WHEN statement.
Table-name
If your query searches more than one table, you must specify the name of the table containing the column which is the named-control- break-object or the name of the column.
Named-control-break-object
If you want the mathematical function to be performed after a control break in your report, you can specify the name of a control break column used in your SORT statement.
BREAKS
An optional word to clarify that the mathematical function will be performed after a control break.
FINISHED
If you want the mathematical function to be performed at the end of your report, specify this keyword after the WHEN.
DO
Tells CA Dataquery that information after this verb describes what type of mathematical function is to be performed and what column will be used in the function. You can specify multiple DO statements for a single WHEN statement. If you have multiple DO statements, you must make certain that you want the specified function performed at the point specified in the WHEN statement.
'Legend-for-display'
You can specify a legend to be printed beside the results of the function calculation. This legend can be up to 64 characters in length and must be enclosed in apostrophes. If you want the current value of the control break printed within this legend, you can include a site-specified heading substitution string like && in the legend.
For example, if you want to calculate an average sales amount for each state in a region, and && were your heading substitution string, you would specify STATE as a control break in the SORT statement that is, SORT (STATE), and as the control break object in the WHEN statement, that is, WHEN STATE BREAKS DO 'AVERAGE SALES FOR &.&'.. You could specify 'AVERAGE SALES FOR &&' as a legend. Each time the state name changes, the legend will identify the state for which the average was calculated. If you do not specify a legend for the DO statement, CA Dataquery prints the function followed by the name of the column on which the function is performed.
See the SYSTEM PROFILE panel in Topic Help to learn what characters are used at your site as a heading substitution string.
FUNCTION
You must identify the function that you want performed. Only one function can be specified in a single DO statement. However, you can use multiple WHEN/DO statements or multiple DO statements for a single WHEN in your query to obtain the desired results for your report. The functions you can perform are described in the next chart.
|
Function |
Entry |
Performs this function at control break or end of report: |
|---|---|---|
|
Average |
AVG |
Averages the values |
|
Count |
CNT |
Counts the number of values |
|
Maximum |
MAX |
Finds the maximum value |
|
Minimum |
MIN |
Finds the minimum value |
|
Sum |
SUM |
Sums the values |
|
Page break |
PAGE-BREAK |
Begins a new page |
Column-Name
You must specify the numeric column for which the function is to be performed. If you specify the name of a column which is a repeating field, you must use subscripts to show which occurrence you want. See Repeating Fields for more information.
PICTURE 'picture-clause'
You can specify an edit pattern to be used when reporting on the function results. See Step 6 for a description of PICTURE and 'picture-clause'.
Procedure
In the last step, you learned how to complete a PRINT statement, telling CA Dataquery how to arrange data on the report, giving it a title, and specifying a total and a PICTURE clause for SHIP-PRICE.
=> --------------------------------------------------------------------------DQD10 DATAQUERY: EDITOR CURRENT TABLE: CAI-DETAIL-TBL ------------------------------------------------------------------------------- NAME: USER-SAMPLE TYPE: QUERY STATUS: PRIVATE DESCRIPTION: TOTAL ORDERS PER CUSTOMER -- AVERAGE PRICE ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+. .. ================================ T O P ===================================== 01 FIND ALL CAI-DETAIL-TBL ROWS 02 WITH SHIP-QTY GT 0 03 SET SHIP-PRICE(7.2) = UNIT-PRICE * SHIP-QTY 04 SORT BY (CUST-ID) 05 PRINT FROM TITLE1 'FILLED CUSTOMER ORDERS' 06 CUST-ID 07 ITM-ID 08 SHIP-QTY 09 UNIT-PRICE 10 (SHIP-PRICE) PICTURE 'Z,ZZZ,ZZ9.99-' .. =========================== B O T T O M ================================== ------------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> DISPLAY COLUMNS <PF4> DISPLAY KEYS <PF5> DISPLAY ALL <PF6> LIST TABLES<PF7> BACKWARD <PF8> FORWARD <PF9> TEMPLATE <PF10> VALIDATE <PF11> RIGHT/LEFT <PF12>PROCESS MODE
|
Copyright © 2014 CA.
All rights reserved.
|
|