DB2 FOR LUW / DB2FORLUW / B10 - DB2

DB2 may incorrectly issue a SQL0119N when a scalar 
aggregate function references a correlated column. This can 
happen when there are at least two scalar aggregate functions 
with correlated column references. 
 
 
Example: 
 
SELECT 
    SUM(case when 99 = (SELECT T1.C2 FROM T1 where T1.C1 = 
T2.C1) then 0 else 1 end), 
    SUM(case when 99 = (SELECT T1.C2 FROM T1 where T1.C1 = 
T3.C1) then 0 else 1 end) 
FROM T2, T3

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 11.1 Mod 2 Fix Pack 2 or higher               * 
****************************************************************

If there is no explicit group by clause, add table references to 
the correlated columns to eliminate correlation within the 
scalar aggregate and also add a DISTINCT to the scalar subquery: 
 
SELECT 
    SUM(case when 99 = (SELECT DISTINCT T1.C2 FROM T1, T2 where 
T1.C1 = T2.C1) then 0 else 1 end), 
    SUM(case when 99 = (SELECT DISTINCT T1.C2 FROM T1, T3 where 
T1.C1 = T3.C1) then 0 else 1 end) 
FROM T2, T3

DB2 Version 11.1 Mod 2 Fix Pack 2 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod2 Fix Pack2 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod2 Fix Pack2 iFix002 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod 3 Fix Pack 3 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix001 for Linux, UNIX, and Windows
 Db2 Version 11.1 Mod3 Fix Pack3 iFix002 for Linux, UNIX, and Windows
 

First fixed in DB2 11.1 Mod 2 Fix Pack 2

not known / see Local fix

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

09.02.2017
28.06.2017
28.06.2017