Connect CDC SQData

The GROUP BY command works in conjunction with the aggregate functions (i.e. SUM, MIN, MAX). This command allows you to group source records based on a field(s) in a source datastore and works in a similar manner as the SQL GROUP BY function, except that this command works against non-relational datastores as well as relational datastores.

Syntax

GROUP BY [<source_datastore>][.<source_description>].<source_field>;

 

Keyword and Parameter Descriptions

<source_datastore>  Optional. Specifies the name of the source datastore that contains the field that will be grouped.

<source_description> Optional. Specifies the name of the description/structure that defines that source data record.

<source_field>  Specifies the name of the source field to be grouped.

 

Example

Group the source data records in datastore SRC_DS by the field DEPT (department). Use the COUNT function to count the number of employees in the department and map the count to the target field EMPL_COUNT.

GROUP BY SRC_DS.DEPT

PROCESS INTO TGT_DS

SELECT

{

   TGT_DS.DEPT_NAME     = CDCIN.DEPT_NAME

   TGT_DS.EMPL_COUNT   = COUNT(CDCIN.EMPL_NUM)

}

FROM CDCIN;

 

When this script is executed, there will be one (1) target record written for each distinct department in the source datastore. These target record will contain the department name and the count of the number of employees within the department.