DB2 FOR LUW / DB2FORLUW / 970 - DB2

SQLCODE SQL0901N may be issued while executing a query that 
satisfies the following conditions: 
- There is an equality predicate (EQPRD) in the query 
- One side of EQPRD is either a constant, host-variable or 
special register (CONST) 
- The other side of EQPRD is a column derived from the output of 
a MAX or MIN aggregate function 
- The MAX or MIN function aggregates the output of a CASE 
expression (CASEEXP) 
- CASEEXP has only 1 WHEN clause and either an ELSE NULL or no 
ELSE clause 
- The WHEN clause is also an EQPRD (WHENPRD) 
- One side WHENPRD is CONST 
- The other side WHENPRD is non-CONST 
- The THEN clause is a CASE expression (NESTCASE), i.e., nested 
inside CASEEXP 
- The THEN and ELSE clause(s) in NESTCASE do not contain 
expressions 
 
An example scenario: 
 
-- DDL 
CREATE TABLE T1 (C1 CHAR(3), C2 VARCHAR(1)); 
INSERT INTO T1 VALUES ('ABC', 'A'); 
INSERT INTO T1 VALUES ('BCD', 'B'); 
 
-- Query 
SELECT CASE_COL_1, 
       CASE_COL_2 
FROM 
(SELECT MAX(CASE WHEN C1 = 'ABC' 
                THEN (CASE WHEN C2 = ' ' 
                           THEN '-' 
                           ELSE C2 
                      END) 
            END) AS CASE_COL_1, 
        MIN(CASE WHEN C1 = 'BCD' 
                THEN (CASE WHEN C2 = ' ' 
                           THEN '-' 
                           ELSE C2 
                      END) 
            END) AS CASE_COL_2 
 FROM   T1 
) T 
WHERE CASE_COL_1 = 'A'; 
 
-- With just one such column, the -901 will be: 
SQL0901N  The SQL statement failed because of a non-severe 
system error. 
Subsequent SQL statements can be processed.  (Reason "no cast 
fcn".) 
SQLSTATE=58004 
 
-- Otherwise, it will be: 
SQL0901N The SQL statement failed because of a non-severe system 
error. 
Subsequent SQL statements can be processed. (Reason "sqlridummy: 
invalid opcode 
8 at offset <nnnn:n>".) SQLSTATE=58004

**************************************************************** 
* USERS AFFECTED:                                              * 
* EE or EEE                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description.                                       * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 Version 9.7 Fix Pack 1.                       * 
****************************************************************

Rewrite EQPRD to a semantically equivalent 
non-relational-operator form. For the query above, this may be 
 
done like so: 
 
SELECT CASE_COL_1, 
 
       CASE_COL_2 
FROM 
(SELECT MAX(CASE WHEN C1 = 'ABC' 
  THEN (CASE WHEN C2 = ' ' 
      THEN '-' 
      ELSE C2 
        END) 
     END) AS CASE_COL_1, 
 
 MIN(CASE WHEN C1 = 'BCD' 
  THEN (CASE WHEN C2 = ' ' 
      THEN '-' 
      ELSE C2 
        END) 
     END) AS CASE_COL_2 
 FROM T1 
) T 
WHERE CASE_COL_1 IN ('A', 'A');

DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 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 10 for Linux, UNIX, and Windows
 

Problem was first fixed in DB2 Version 9.7 Fix Pack 1.

not known / see Local fix

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

20.08.2009
19.02.2010
19.02.2010

9.7.FP1