DQL Statements › WHEN/DO Statements › End of Report

End of Report

Purpose

You can specify that a mathematical function be performed at the end of a report.

Syntax Diagram

The syntax for specifying a mathematical function when the report is finished is:

►►─ WHEN FINISHED DO ─┬───────────────────┬─ function ────────────────────────►
                      └─ 'report-legend' ─┘

 ►─┬─ 'table-name ─┬────────────┬─ 'column-name ─┬────────────────────────────►
   │               └─ (status) ─┘                │
   └─ result-name ───────────────────────────────┘

 ►─┬──────────────────────────┬───────────────────────────────────────────────►◄
   └─ PICture 'edit-pattern' ─┘

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 preceding WHEN/DO statement format.

FINISHED

Refers to end of report and implies grand totals on sums and overall averages, maximums, and minimums.

'report-legend'

Specifies a legend to be printed beside the results of the function calculation. Can be up to 64 characters in length. 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: Do not use the heading substitution string in a legend for an end-of-report function.

function

Specifies the function to be performed. The following describes each function you can perform and the entry in your query.

AVG

Averages the values for the specified numeric column.

CNT

Counts the number of occurrences of the specified column.

MAX

Finds the maximum value for the specified numeric column.

MIN

Finds the minimum value for the specified numeric column.

SUM

Sums the values for the specified numeric column.

table-name

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.

(status)

(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.

TEST

Finds the named table in the first test version that meets the specifications.

Tnnn

(Where nnn refers to the version number of the test status you want.) Finds the named table in the specified test version.

PROD

PRODuction is the default status if not specified.

column-name

Specifies the name of the column on which the function is to be performed. 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. (See the CA Dataquery User Guide for more information.)

result-name

Specifies the name of the temporary result on which the function is to be performed.

PICTURE

(Optional) Specifies that the data should be formatted according to the edit pattern specified next.

'edit-pattern'

A series of characters that specify how you want the data to be formatted. See PICTURE Clause.

Example

The following query illustrates a mathematical function performed at the end of the report.

    FIND 10 CAI-SLSHST-REC ROWS
        WITH SHIP-QTY > 100
      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 FINISHED     DO SUM SHIP-QTY

Output

The following screen is sample output generated from executing the preceding query.

 =>
01/02/2010                           CA Dataquery                    PAGE 1
17:36:57                                                                  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
 
   09312  00012    O50006      0000100    00014.75    00001475.00   00002.75
          00015    O90001      0000010    00090.00    00000900.00   00002.75
 
  TOTAL ORD-ID 08811
                                                    * 00001375.00
* GRAND TOTAL
                                                    * 00004516.75
  SUM SHIP-QTY  0000323
 -------------------------------- 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