The following restrictions apply to aggregate functions used to define aggregate join indexes:
- Only the COUNT and SUM functions, in any combination, are valid.
- COUNT DISTINCT and SUM DISTINCT are not valid.
- To avoid overflow problems, type the COUNT and SUM columns in a join index definition as FLOAT or DECIMAL(38,0) when the data type of the base table column is INTEGER or FLOAT, and as DECIMAL(38,xx) when the data type of the base table column is DECIMAL(NN,xx).
| Situation | System Behavior |
|---|---|
| You do not define an explicit data type for a COUNT or SUM column that has a data type of INTEGER or FLOAT | System assigns FLOAT data type to column. |
| You do not define an explicit data type for a COUNT or SUM column that has a data type of DECIMAL | System assigns DECIMAL(38,xx) data type to column. |
| You define a COUNT or SUM column that has a data type of INTEGER as anything other than FLOAT and DECIMAL(38,0) | System returns an error and does not create the aggregate join index. |
| You define a COUNT or SUM column that has a data type of DECIMAL as anything other than DECIMAL(38,xx) | System returns an error and does not create the aggregate join index. |
| You define a COUNT or SUM column that has a data type of FLOAT as anything other than FLOAT | System returns an error and does not create the aggregate join index. |