Purpose
To specify the group-by fields used for grouping records for an aggregation.
Format
/GROUPBY FIELDS field [, field …] Where
| field | = | field_name [datetime_period] [COLLATINGSEQUENCE sequence] [sense] |
| datetime_period | = | {BYYEAR } {BYMONTH } {BYDAY } {BYHOUR } {BYMINUTE} {BYSECOND} |
| sequence | = | {LOCALE } {ASCII } {FASCII } {EBCDIC } {FEBCDIC } {MULTINATIONAL } {seq_name } |
| sense | = | {ASCENDING } {DESCENDING} |
Arguments
| field_name | The name of a field. For a summary of valid naming and formatting conventions for identifiers and constants, see syntax reference in the Connect help. |
| seq_name | Name of a custom collating sequence defined using
/COLLATINGSEQUENCE. |
Location
This option may appear anywhere in the task definition.
Notes
The /GROUPBY option is is applicable to aggregate tasks only and allows you to group source records for an aggregation. With the /GROUPBY option you group source records based on specified fields and values and organize the groups by date/time period and by ascending or descending order.
For information on specifying key fields for ordering input records for sort and merge tasks, see /KEYS.
Fields
A group by field can be one of the following:
- A field in the record as it is obtained from the source
- A value, derived from field values and/or constants
Date/time Specification
When grouping records by date, you have the option of grouping by all components that comprise the date/time field or of grouping at a higher granularity. When the date.time field contains year, month, day, hour, minute and second, for example, you can group records such that all components are significant. In addition, you can restrict the signifiant components by specifying a datetime_period argument, such as BYHOUR, which causes Connect ETL to group the records by hour and ignore the minutes and seconds date components.
Example
/GROUPBY FIELDS recordlayout.id, recordlayout1.name/GROUPBY FIELDS recordlayout.start_date BYMONTHThis option groups records by the month of the start_date