DB2 FOR LUW / DB2FORLUW / 970 - DB2

A SQL Procedure that has an SQL statement that uses the 
combination of a GROUP BY and a parameter value will fail with a 
SQL0119N error: 
 
SQL0119N  An expression starting with "C1" 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.  LINE NUMBER=12. 
SQLSTATE=42803 
 
For example the following CREATE PROCEDURE statement will fail: 
 
CREATE TABLE testtbl (C1 VARCHAR(30), C2 DECFLOAT)@ 
 
CREATE PROCEDURE testproc() 
LANGUAGE SQL 
BEGIN 
  DECLARE myval VARCHAR(150); 
  DECLARE isnull VARCHAR(150); 
   SELECT COALESCE(C1,isnull) 
   INTO myval 
   FROM testtbl 
   GROUP BY COALESCE(C1,isnull); 
  RETURN 0; 
END 
@

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7 fix pack 7                        * 
****************************************************************

Move the local variable into the from-clause and replace both 
references of the variable with a new correlation name, for 
example: 
 
CREATE PROCEDURE testproc() 
LANGUAGE SQL 
BEGIN 
  DECLARE myval VARCHAR(150); 
  DECLARE isnull VARCHAR(150); 
   SELECT COALESCE(C1,T.ISNULL) 
   INTO myval 
   FROM testtbl, TABLE( VALUES( isnull ) ) T(ISNULL) 
   GROUP BY COALESCE(C1,T.ISNULL); 
  RETURN 0; 
END 
@

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
 

First fixed in DB2 version 9.7 fix pack 7

not known / see Local fix

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

30.03.2012
11.12.2012
11.12.2012

9.7.FP7