WHEN/DO Statements (DQL)

The WHEN/DO statement specifies when a mathematical function or page break is to be performed and what column will be the object of the mathematical function.
datacom
The WHEN/DO statement specifies when a mathematical function or page break is to be performed and what column will be the object of the mathematical function.
Since only one PRINT statement can be present in the query, the PRINT statement must be completed before the WHEN/DO statement is given. There can be multiple WHEN statements in the query, and there may be multiple DO statements for any WHEN statement.
You can perform mathematical functions when a control break occurs, or at the end of the report. Mathematical functions which you can specify in the DO statement are AVG, CNT, MAX, MIN, and SUM. See Designating Control Breaks for more information.
A DO statement can use the following data types: CHAR NUM, DEC, BIN, FLOAT-PT SHRT, FLOAT-PT LONG, EXT, 1/2 BIN, FUL BIN, DBL CHAR, DBL MIXED, KANJI.
Topics discussed on this page include:
 
 
Syntax Diagram
The following diagram shows the syntax of a WHEN/DO statement.
►►─ 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.
To simplify discussion, we present the options of the WHEN/DO statement in the following sections.
Specifying Functions at Control Breaks
You can have 
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.
The following explains the previous syntax diagram for WHEN/DO statements.
  •  
     
    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 
    Datacom 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.
     The status must be enclosed in parentheses, and there must be no spaces between the table name and the left parenthesis.
  •  
     
    named-control-break
     
    Specifies the name of a column or key which was designated as a control-break in a SORT statement.
  •  
    BREAKS
    Used only for readability.
  •  
     
    'report-legend'
     
    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.
  •  
     
    function
     
    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.
  •  
    PAGE-BREAK
    Specifies that 
    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.)
  •  
     
    column-name
     
    Specifies the name of the column on which to perform the specified function. The named column must be defined in 
    Datacom 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)
  •  
     
    result-name
     
    Specifies the name of the temporary result on which to perform the specified function.
  •  
    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 (DQL).
 
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
End of Report
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.
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.
     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 
    Datacom 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.
     The status must be enclosed in parentheses, and there must be no spaces between the table name and the left parenthesis.
  •  
     
    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. 
  •  
     
    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 (DQL).
 
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
Page Break
In addition to performing mathematical functions on a control break, you can also specify a page break. This option allows you to start report output related to a specified control break on a new page.
 
Syntax Diagram
 
The syntax for specifying a page break at a control break is:
►►─ WHEN ─
table-name
─┬────────────┬─
named-control-break
BREAKS ─────────────► └─ (
status
) ─┘ ►─ DO PAGE-BREAK ─┬──────────────────────────┬───────────────────────────────►◄ └─ PICture '
edit-pattern
' ─┘
Repeat WHEN statement once for each function or page break wanted. Multiple DO statements may be used with one WHEN statement.
The following explains the preceding format.
  •  
     
    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 
    Datacom 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.
     The status must be enclosed in parentheses, and there must be no spaces between the table name and the left parenthesis.
  •  
     
    named-control-break
     
    Specifies the name of a column or key which was designated as a control-break in a SORT statement.
  •  
    BREAKS
    Used only for readability.
  •  
    PAGE-BREAK
    You can have 
    Dataquery
     start a new page or new screen every time the value in a specific control break changes. (WHEN/DO PAGE-BREAK does not operate with totals-only.)
  •  
    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 (DQL).
Specifying Multiple DO Statements
The following query illustrates multiple DO statements for a single WHEN statement. The multiple DO statements perform mathematical functions and a page break when a control break is encountered during execution.
 
Sample
 
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 UNIT-PRICE DO SUM CAI-SLSHST-REC SHIP-QTY DO PAGE-BREAK
 
Output
 
This screen is the first page of sample output generated from executing this query.
 
Sample (Continued)
 
=> 01/02/2010 CA Dataquery PAGE 1 18:21:32 DETAIL ORD-ID SHIP-ID ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT ------ ------- ---------- -------- ---------- ------------- --------- 07333 00001 O10001 0000006 00998.50 00005991.00 00010.00 00002 O30012 0000001 01000.00 00001000.00 00003.00 TOTAL ORD-ID 07333 * 00006991.00 MAXIMUM UNIT-PRICE 01000.00 SUM SHIP-QTY 0000007 -------------------------------- MORE .... --------------------------------- <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
 
Sample (Continued)
 
This screen is a later page of sample output for the preceding example.
=> 01/02/2010 CA Dataquery PAGE 3A 18:21:32 DETAIL ORD-ID SHIP-ID ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT ------ ------- ---------- -------- ---------- ------------- --------- 09311 00001 C10001 0000023 00021.00 00000483.00 00032.00 TOTAL ORD-ID 09311 * 00000483.00 MAXIMUM UNIT-PRICE 00021.00 SUM SHIP-QTY 0000023 -------------------------------- MORE .... --------------------------------- <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
 
Sample (Continued)
 
This screen is the last page of output for the preceding example.
=> 01/02/2010 CA Dataquery PAGE 5A 18:21:32 DETAIL ORD-ID SHIP-ID ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT ------ ------- ---------- -------- ---------- ------------- -------- * GRAND TOTAL * 00056053.40 -------------------------------- 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
WHEN/DO Limitations
The following is an important limitation to observe when constructing a WHEN/DO statement.
  • Legends in DO statements cannot exceed 64 characters.