DB2 FOR LUW / DB2FORLUW / 970 - DB2

When using a database created with NUMBER compatibility enabled, 
queries that use the distinct aggregation function AVG( DISTINCT 
column_or_expression ) may receive a SQL0901N error when the 
column or expression used in the function is typed as a user 
defined distinct type. 
 
For example 
 
create distinct type t_dec as decimal(10,3) with comparisons; 
create function avg(t_dec) returns t_dec source 
avg(decimal(10,3)); 
create table tbl1 (c1 integer, c2 t_dec); 
select avg(distinct c2) from tbl1 group by c1;

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* .                                                            * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Apply version 9.7 Fix Pack 5 on the database server.         * 
****************************************************************

SQL0901N can be avoided by explicitly casting the column to the 
base type in the query. For example: 
select avg(distinct  DECIMAL(c2,10,3)) from tbl1 group by c1

DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in Version 9.7 Fix Pack 5

not known / see Local fix

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

12.07.2011
19.12.2011
19.12.2011

9.7.FP5