SQL aggregate functions supported by the IMS JDBC drivers

The IMS™ classic JDBC driver and the IMS Universal JDBC driver support SQL aggregate functions and related keywords.

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

Recommendation: Start of change

Customers using these APIs should migrate to the IMS Universal drivers. IMS Version 13 is the last release to support the IMS classic Java™ APIs. Because the IMS Universal JDBC driver provides improved support for standard SQL syntax, use the IMS Universal JDBC driver to develop JDBC applications that access IMS. End of change

Restriction: Start of change

The supported SQL aggregate functions accept only a single field name in a segment as the argument (the DISTINCT keyword is not allowed). The exception is the COUNT function, which allows the DISTINCT keyword. This restriction applies to the IMS classic JDBC driver, but not to the IMS Universal JDBC driver. End of change

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