|
Problem IC70754
|
Status: Closed |
EXTENDING SUPPORT OF SUBQUERY IN AGGREGATE FUNCTIONS |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Starting from version 9.7 Fix Pack 4, DB2 will support subquery
and scalar subselect in aggregate functions.
For example:
This SQL statement below returns the sum of the sales amount
group by department if the department number exists in table
DEPARTMENT or 0 if the department number does not exist in table
DEPARTMENT.
SELECT S.DEPTNO, SUM (CASE WHEN EXISTS(SELECT 1 FROM
DEPARTMENT D WHERE D.DEPTNO = S.DEPTNO) THEN S.AMOUNT ELSE 0
END)
FROM SALES S
GROUP BY S.DEPTNO
;
Assuming column DEPARTMENT.DEPTNO is defined NOT NULL, the above
statement can also be written without the subquery in aggregate
function as below:
WITH D(DEPTNO) AS (SELECT DISTINCT DEPTNO FROM DEPARTMENT)
SELECT S.DEPTNO, SUM(CASE WHER D.DEPTNO IS NOT NULL THEN
S.AMOUNT ELSE 0 END)
FROM SALES S LEFT JOIN D
ON S.DEPTNO = D.DEPTNO
GROUP BY S.DEPTNO
; |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* User working with subqueries in aggregate functions *
****************************************************************
* PROBLEM DESCRIPTION: *
* Starting from version 9.7 Fix Pack 4, DB2 will support *
* subquery *
* and scalar subselect in aggregate functions. *
* *
* *
* *
* For example: *
* *
* *
* *
* This SQL statement below returns the sum of the sales amount *
* *
* group by department if the department number exists in table *
* *
* DEPARTMENT or 0 if the department number does not exist in *
* table *
* DEPARTMENT. *
* *
* *
* *
* SELECT S.DEPTNO, SUM (CASE WHEN EXISTS(SELECT 1 FROM *
* *
* DEPARTMENT D WHERE D.DEPTNO = S.DEPTNO) THEN S.AMOUNT ELSE 0 *
* *
* END) *
* *
* FROM SALES S *
* *
* GROUP BY S.DEPTNO *
* *
* ; *
* *
* *
* *
* Assuming column DEPARTMENT.DEPTNO is defined NOT NULL, the *
* above *
* statement can also be written without the subquery in *
* aggregate *
* function as below: *
* *
* *
* *
* WITH D(DEPTNO) AS (SELECT DISTINCT DEPTNO FROM DEPARTMENT) *
* *
* SELECT S.DEPTNO, SUM(CASE WHER D.DEPTNO IS NOT NULL THEN *
* *
* S.AMOUNT ELSE 0 END) *
* *
* FROM SALES S LEFT JOIN D *
* *
* ON S.DEPTNO = D.DEPTNO *
* *
* GROUP BY S.DEPTNO *
* *
* ; *
****************************************************************
* RECOMMENDATION: *
* Install 9.7 fixpack 4 *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
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 6 for Linux, UNIX, and Windows
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
|
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 24.08.2010
28.04.2011
28.04.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.4
|
 |
