7.63.2 KEEP_COUNT Comments / Warnings
This applies to the commands KEEP_AVG, KEEP_COUNT, KEEP_MAX, KEEP_MIN and KEEP_TOTAL.
The processing logic used by these commands is best demonstrated by an example.
Consider the following SELECT loop that selects and prints invoice detail lines:
DEF_LINE NAME(#DETAIL) FIELDS(#INVNUM #VALUE)
DEF_BREAK NAME(#TOTAL) FIELDS(#INV_TOTAL) TRIGGER_BY(#INVNUM)
DEF_BREAK NAME(#GRAND) FIELDS(#GRD_TOTAL)
SELECT FIELDS(#INVNUM #VALUE) FROM_FILE(INVLIN)
KEEP_TOTAL OF_FIELD(#VALUE) IN_FIELD(#INV_TOTAL)BY_FIELD(#INVNUM)
KEEP_TOTAL OF_FIELD(#VALUE) IN_FIELD(#GRD_TOTAL)
PRINT LINE(#DETAIL)
ENDSELECT
ENDPRINT
In highly simplified terms, what actually happens is:
Set #INV_TOTAL and #GRD_TOTAL to zero
---> Select next record
| If #INVNUM has changed set #INV_TOTAL to zero
| Add #VALUE to #INV_TOTAL
| Add #VALUE to #GRD_TOTAL
| If #INVNUM has changed, print "trailing" break
| line #TOTAL
| Print the #DETAIL line
---- Endselect
Print the grand total line #GRAND
Although this logic is highly simplified, it demonstrates the basic processing logic. This is shared by all KEEP_XXXXX commands and their relationship to the SELECT and SELECTLIST commands. Only the method of "accumulating" the result as an average, count, maximum, minimum or total varies.
Refer to the DEF_BREAK command for details of how and when the break lines are "triggered" and why the correct #INV_TOTAL value is always printed, even though it has apparently been cleared and reset by the KEEP_TOTAL command before it is printed.
If the KEEP_XXXXX command is within a condition (like IF or CASE/WHEN) and BY_FIELD is used the totals will not be reset correctly.