Purpose
To define and customize the computation of a named summarized value.
Format
/SUMMARY sum_name sum_spec
where
| sum_spec | = | {format_customization} {collation_customization} {FIRST value } {LAST value } {ANY value } | |
| format_customization | = | {TOTAL value } {AVERAGE value} {COUNT } | [sum_format] |
| collation_customization | = | {MAXIMUM value} {MINIMUM value} | [COLLATINGSEQUENCE sequence] |
| sum_format | = | {data_format length} {length data_format} |
Arguments
| sum_name | The name of the summarized value. |
| value | The summarized value that is being defined. |
| sequence | Name of a standard collating sequence or a customized collating sequence defined using /COLLATINGSEQUENCE. |
| data_format | The data type to use while computing this summary. See Data types reference/Connect ETL data types in the Connect help for the list of supported data types. |
| length | The data length to use while computing this summary. The length is in bytes for all data types except BIT, when the length is in bits. See Data types reference/Connect ETL data types in the Connect help for the length of a field with one of the supported data types. When the length of a date/time field is increased, it is padded on the right with spaces. |
Location
The option may appear anywhere in the task definition.
Notes
The /SUMMARY option is is applicable to aggregate tasks only and allows you to define and customize the computation of a summarized value. You can define any number of summarizations on any number of fields provided that the summarized value is unique.
Summarized Values
In an aggregate task, to use any summarized field in the output, you must define it using the /SUMMARY option.
Summarized values can be defined using the following keywords, which specify the type of summarization that is applied:
- FIRST – first field value that occurs among all records in the set. The FIRST keyword can be applied only to sources with a sequential organization, not to indexed files or database tables, for example.
- LAST – last field value that occurs among all records in the set. The LAST keyword can be applied only to sources with a sequential organization, not to indexed files or database tables, for example.
- ANY – field value from any record in the set. The record from which the value originates is arbitrary.
- TOTAL – summation of the field values for all records in the set.
- AVERAGE – average field value for all records in the set.
- COUNT – value of the number of records in the set.
- MAXIMUM –largest field value among all records in the set.
- MINIMUM – smallest field value among all records in the set.
NULL Value
When a nullable field is summarized, and one or more records in an equal-keyed record set have the NULL value in that field, the record does not contribute to the summary. When all records in the set have a NULL field value, the summary value is NULL.
Collating sequence
A collating sequence specifies the collating order and comparison rules of ASCII encoded character data. A task wide default can be set via the /COLLATINGSEQUENCE DEFAULT option. If a default is not set, the ASCII standard sequence is assumed to be the default. LOCALE encoded data is collated using the collating sequence of the current locale. Unicode encoded (UTF-*) fields are always collated according to the Unicode collation algorithm, described at http://www.unicode.org/unicode/reports/tr10/, using a collation strength of three levels for comparisons.
To define a customized collating sequence, whose name can also be specified after the COLLATINGSEQUENCE keyword within the /SUMMARY option, use the /COLLATINGSEQUENCE option.
Example
/SUMMARY sum COUNT EN 10/SUMMARY total_value TOTAL recordlayout.f1 EN 5/SUMMARY max_value MAXIMUM recordlayout.f2 COLLATINGSEQUENCE ASCIIThis option defines a summary value that results in the largest of the values in the field recordlayout.f2 in all of the records that are grouped together.