SQL aggregate functions supported for COBOL

SQL aggregate functions are supported for COBOL and .NET applications (by using IMS Enterprise Suite IMS Data Provider for Microsoft .NET).

  • AVG
  • COUNT
  • MAX
  • MIN
  • SUM
These functions work with the ORDER BY clause, as well as GROUP BY:
  • ORDER BY
    • ASC
    • DESC
  • GROUP BY
Restriction: The supported SQL aggregate functions accept only a single field name in a segment as the argument (the DISTINCT keyword is not allowed).

The following table shows the data types of the fields that are accepted by the aggregate functions, along with the resulting data type in the ResultSet.

Table 1. Supported SQL aggregate functions and their supported data types
Function Argument types Results and result types
AVG All supported numeric data types for SQL statements in COBOL programs are supported, including TINYINT, SMALLINT, INTEGER, BIGINT, Zoned Decimal, and Packed Decimal
  • The function is applied to the set of values derived from the argument values by excluding null values.
  • For INTEGER, the fractional part of the average is discarded. The result can be null.
  • For non-DECIMAL data types, the result data type is always LONG.
  • If the data type of the argument value is DECIMAL, the result is packed decimal. The scale of the result is the same as the scale of the argument value, and the precision of the result is 31.
  • If the function is applied to an empty set, the result is the null value.
  • The averaged value must be within the range of the data type of the result.
COUNT Any supported data type
  • NULL values are not counted when the total number of values in a given column is counted (COUNT(column)).
  • NULL values are counted when the number of rows in a table is counted (COUNT(*)).
  • COUNT(*) of an empty table returns one row with a value of 0.
MAX TINYINT, INTEGER, BIGINT, Zoned Decimal, Packed Decimal, CHAR, BINARY, DATE, TIME, and TIMESTAMP
  • Character string arguments and binary string arguments cannot have a length attribute greater than 32704.
  • The data type of the result and its other attributes (for example, the length and CCSID of a string or a datetime value) are the same as the data type and attributes of the argument values.
  • The result can be null.
  • The function is applied to the set of values derived from the argument values by the elimination of null values.
  • If the function is applied to an empty set, the result is the null value.
MIN TINYINT, INTEGER, BIGINT, Zoned Decimal, Packed Decimal, CHAR, BINARY, DATE, TIME, and TIMESTAMP
  • The data type of the result and its other attributes (for example, the length and CCSID of a string or a datetime value) are the same as the data type and attributes of the argument values.
  • The result can be null.
  • The function is applied to the set of values derived from the argument values by the elimination of null values.
  • If the function is applied to an empty set, the result is the null value.
SUM All supported numeric data types for SQL statements in COBOL programs are supported, including TINYINT, SMALLINT, INTEGER, BIGINT, Zoned Decimal and Packed Decimal
  • The sum must be within the range of the data type of the result.
  • The function is applied to the set of values from the argument values by eliminating null values.
  • If the function is applied to an empty set, the result is the null value.
  • The order in which the summation is performed is undefined, but every intermediate result must be within the range of the result data type.
  • If the data type of the argument value is DECIMAL, the result is packed decimal. The scale of the result is the same as the scale of the argument value, and the precision of the result is 31.

Column names generated by aggregate functions

The generated column name from an aggregate function is a combination of the aggregate function name and the field name, separated by an underscore character (_). For example, the statement SELECT MAX(age) results in a column name MAX_age.

If the aggregate function argument field is table-qualified, the generated column name is the combination of the aggregate function name, the table name, and the column name, separated by underscore characters (_). For example, SELECT MAX(Employee.age) results in a column name MAX_Employee_age.

The aggregate function is executed first, and then the required number of rows of result are fetched from the result set.

Using the ORDER BY and GROUP BY clauses

The field names that are specified in a GROUP BY or ORDER BY clause must match exactly the field name that is specified in the SELECT statement. In order to GROUP BY properly, the fields specified in SELECT list must also be specified in the GROUP BY list. 

SELECT HOSPNAME, COUNT(PATNAME) FROM PCB01.HOSPITAL, PATIENT GROUP BY HOSPNAME
 ORDER BY HOSPNAME
Restriction: Aggregate functions cannot be used in the GROUP BY or ORDER BY statements. For example, GROUP BY COUNT(PATNAME) or ORDER BY AVG(COST) is not supported.