DB2 FOR LUW / DB2FORLUW / A50 - DB2

The problem may occur when the following conditions are met: 
 
1) A select list of the SQL statement contains a CASE expression 
2) WHEN clause contain an uncorrelated subquery with a 
quantified predicate or IN predicate 
3) THEN clause contains an aggregate function 
 
For example, 
 
select case 
                when 1 != some(select t1.c1 from t1) then 
max(t2.c2) 
           end 
from t2 
 
When this query is executed, DB2 will return the following 
error: 
 
SQL0119N  An expression starting with "" specified in a SELECT 
clause, HAVING 
clause, or ORDER BY clause is not specified in the GROUP BY 
clause or it is in 
a SELECT clause, HAVING clause, or ORDER BY clause with a column 
function and 
no GROUP BY clause is specified.  SQLSTATE=42803 
 
This APAR will return a correct result instead of raising an 
error.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 10.5 Fix Pack 4.                      * 
****************************************************************

If a CASE expression contains multiple WHERE clauses, changing 
their order so that a WHEN clause with an aggregate functions is 
followed by a WHEN clause with an uncorrelated subquery may fix 
the problem (but it also may impact query performance). 
 
If the order cannot be changed, then no work around exists for 
this problem.

DB2 Cancun Release 10.5.0.4 (also known as Fix Pack 4) for Linux, UNIX, and Windows
 DB2 Version 10.5 Fix Pack 9 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 Version 10.5 Fix Pack 4

not known / see Local fix

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

28.05.2014
08.09.2014
08.09.2014