SQL aggregate functions supported by the IMS JDBC drivers

The IMS Universal JDBC driver supports SQL aggregate functions and related keywords.

  • AS
  • AVG
  • COUNT
  • GROUP BY
  • MAX
  • MIN
  • ORDER BY
    • ASC
    • DESC
  • SUM

The ResultSet type for aggregate functions and ORDER BY and GROUP BY clauses is always TYPE_SCROLL_INSENSITIVE.

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 type Result type
SUM and AVG Byte Long
Short Long
Integer Long
Long Long
BigDecimal Double-precision floating point
Single-precision floating point Double-precision floating point
Double-precision floating point Double-precision floating point
MIN and MAX Any type except BIT, BLOB, or BINARY Same as argument type
COUNT Any type Long

Column names generated by aggregate functions

The ResultSet 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. Use this column name in all subsequent references—for example, resultSet.getInt("MAX_age").

If the aggregate function argument field is table-qualified, the ResultSet 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.

Using the AS clause

You can use the AS keyword to rename the aggregate function column in the result set or any other field in the SELECT statement. You cannot use the AS keyword to rename a table in the FROM clause. When you use the AS keyword to rename the column, you must use this new name to refer to the column. For example, if you specify SELECT MAX(age) AS oldest, a subsequent reference to the aggregate function column is resultSet.getInt("oldest").

If you are using the IMS Universal JDBC driver and you specified a SELECT query with column names renamed by an AS clause, you can only refer to the field in the resulting ResultSet by the AS rename. However, in the rest of your SELECT query, in the WHERE, ORDER BY, and GROUP BY clauses, you can use either the original column name or the AS rename.

Using the ORDER BY and GROUP BY clauses

Important: 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.
When using the IMS Universal JDBC driver, the following queries with the ORDER BY and GROUP BY clauses are valid: 
SELECT HOSPNAME, COUNT(PATNAME) AS PatCount FROM PCB01.HOSPITAL, PATIENT 
GROUP BY HOSPNAME ORDER BY HOSPNAME
SELECT HOSPNAME, COUNT(DISTINCT PATNAME) AS PatCount FROM PCB01.HOSPITAL, 
PATIENT GROUP BY HOSPNAME ORDER BY HOSPNAME

Using the COUNT function with DISTINCT

When using the IMS Universal JDBC driver, the COUNT aggregate function can be qualified with the DISTINCT keyword. For example, the following query returns all hospital names listed in ascending order along with the number of distinct patient names from that hospital. The COUNT aggregate function generates a column name COUNT_DISTINCT_PATNAME .
SELECT HOSPNAME, COUNT(DISTINCT PATNAME)FROM PCB01.HOSPITAL, PATIENT 
GROUP BY HOSPNAME ORDER BY HOSPNAME