Enhancement to the SUMPREFIX Parameter

How to:

The SUMPREFIX parameter allows you to specify which value will be displayed when aggregating an alphanumeric or smart date field in the absence of any prefix operator. The default value is LST, which will return the physical last value within the sort group. FST has been available, which will return the first physical value in the sort group. The setting has been enhanced to include MIN and MAX to return either the minimum value or maximum value within the sort group.

MIN and MAX correspond to the SQL MIN and MAX functions and can be easily converted to SQL when optimizing a request against a relational database.

Syntax: How to Control Display of Aggregated Alphanumeric or Smart Date Fields

SET SUMPREFIX = {FST|LST|MIN|MAX}

where:

FST

Displays the first value when alphanumeric or smart date data types are aggregated.

LST

Displays the last value when alphanumeric or smart date data types are aggregated. LST is the default value.

MIN

Displays the minimum value in the sort order set by your server code page and configuration when alphanumeric or smart date data types are aggregated.

MAX

Displays the maximum value in the sort order set by your server code page and configuration when alphanumeric or smart date data types are aggregated.

Example: Displaying the Minimum Value for an Aggregated Alphanumeric Field

The following request sets SUMPREFIX to MIN and displays the aggregated PRODUCT_CATEGORY and DAYSDELAYED values as well as the minimum, maximum, first, and last PRODUCT_CATEGORY values. In each row, the aggregated PRODUCT_CATEGORY value matches the MIN.PRODUCT_CATEGORY value. The DAYSDELAYED numeric field is not affected by the SUMPREFIX value and is aggregated.

SET SUMPREFIX = MIN
TABLE FILE wf_retail_lite
SUM PRODUCT_CATEGORY DAYSDELAYED MIN.PRODUCT_CATEGORY MAX.PRODUCT_CATEGORY 
     FST.PRODUCT_CATEGORY LST.PRODUCT_CATEGORY
BY BRAND
WHERE BRAND GT 'K' AND BRAND LT 'U'
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

WebFOCUS

Feedback