DB2 FOR LUW / DB2FORLUW / 970 - DB2

Under rare scenarios, DB2 might produce SQL0901N error message 
if the following conditions are true: 
1. The query contains a group by clause 
2. The output column list contains an expression 
3. An order by clause is defined in the same subselect as the 
expression 
4. The order by clause contains a aggregation function of the 
expression in 3. 
 
eg. 
SELECT 
  (  CASE   WHEN flag = 1   THEN    ID2  ELSE ''   END) AS ID 
FROM 
  ( 
  SELECT 
  ID as ID2, 
  1 as flag 
  FROM    temp 
  ) a 
GROUP BY 
  (  CASE   WHEN flag = 1   THEN  ID2  ELSE ''   END) 
ORDER BY 
  min(ID) desc; 
 
Error message: 
SQL0901N  The SQL statement failed because of a non-severe 
system error. Subsequent SQL statements can be processed. 
(Reason "QNC in column function references complex expression".) 
SQLSTATE=58004 
 
db2diag.log messages: 
 
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650 
MESSAGE : ZRC=0x8031000F=-2144272369=SQLNN_E_AMBIG 
          "Conflicting or ambiguous elements in the command or 
an 
internal object" 
DATA #1 : String, 62 bytes 
An unexpected error was detected during statement compilation. 
DATA #2 : Boolean, 1 bytes 
true 
DATA #3 : Boolean, 1 bytes 
false 
DATA #4 : Boolean, 1 bytes 
false 
DATA #5 : Boolean, 1 bytes 
false 
DATA #6 : Hex integer, 4 bytes 
0x00000000 
DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes 
 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: -901   sqlerrml: 52 
 sqlerrmc: QNC in column function references complex expression 
 sqlerrp : SQLNQ002 
 sqlerrd : (1) 0x801A006D      (2) 0x00000000      (3) 
0x00000000 
           (4) 0x00000000      (5) 0xFFFFFEDE      (6) 
0x00000000 
 sqlwarn : (1)      (2)      (3)      (4)        (5)       (6) 
           (7)      (8)      (9)      (10)        (11) 
 sqlstate: 
 DATA #8 : Hex integer, 4 bytes 
0x00000040 
DATA #9 : String, 244 bytes 
Compiler error stack for rc = -2144272369: 
sqlnn_cmpl[300] 
sqlnp_main[250] 
sqlnp_parser[510] 
sqlnp_smactn[100] 
sqlnq_sem_function_call[100] 
sqlnq_proc_agf[290]

**************************************************************** 
* USERS AFFECTED:                                              * 
* DB2 UDB Users                                                * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 v9.7 Fixpack 9                                * 
****************************************************************

Add the aggregate expression in the order by clause into the 
select list. 
eg. rewritten query: 
select ID 
from ( 
SELECT 
  (  CASE  WHEN flag = 1  THEN    ID  ELSE ''  END) AS ID , 
  min ((  CASE  WHEN flag = 1  THEN    ID  ELSE ''  END)) as 
min_ID 
FROM 
  ( 
  SELECT 
    ID as ID2, 
    1 AS flag 
  FROM 
    CAPO_IDGCBA.RDE_MONITOR_VIEW rqi 
  ) a 
  GROUP BY 
     ( CASE  WHEN flag = 1  THEN  ID  ELSE ''  END) 
 ) 
ORDER BY 
  min_ID desc;

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 first fixed in v9.7 Fixpack 9

keiner bekannt / siehe Local-Fix

Vorgänger  : APAR is sysrouted TO one or more of the following: IC90093 
Nachfolger : 

Datum - Problem gemeldet    :
Datum - Problem geschlossen :
Datum - der letzten Änderung:

07.02.2013
30.12.2013
30.12.2013

9.7.FP9