|
Problem IC62652
|
Status: Closed |
SQL0901N WHEN QUERY USES MULTIPLE XMLAGG WITH ORDER BY CLAUSE IN THE SAME GROUP BY STATEMENT AND INPUT ARGUMENT NOT NULLABLE. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
When a query uses multiple XMLAGG with ORDER BY clause in the
same GROUP BY statement and the input argument to XMLAGG is not
nullable, the user might get error SQL0901N "Subsequent SQL
statements can be processed.(Reason "no cast fcn".)".
For example, with this table definition,
CREATE TABLE customer
(cust_id INTEGER NOT NULL WITH DEFAULT
,branch# INTEGER NOT NULL WITH DEFAULT
,balance DECIMAL(12,2) NOT NULL WITH DEFAULT
,firstnme VARCHAR(20) NOT NULL WITH DEFAULT
,middlnme VARCHAR(20) NOT NULL WITH DEFAULT
,lastname VARCHAR(20) NOT NULL WITH DEFAULT
,PRIMARY KEY (cust_id));
COMMIT;
If the user issues the following query:
SELECT branch# AS branch#
,COUNT(*) AS #cust
,MAX(firstnme) AS max_fname
,MAX(middlnme) AS max_mname
,MAX(lastname) AS max_lname
,XMLAGG(XMLTEXT(firstnme || ' ')
ORDER BY firstnme) AS fname2
,XMLAGG(XMLTEXT(middlnme || ' ')
ORDER BY middlnme) AS mname2
FROM customer
GROUP BY branch#
ORDER BY branch#;
You will receive the following error:
SQL0901N The SQL statement failed because of a non-severe
system error. Subsequent SQL statements can be processed.
(Reason "no cast fcn".) SQLSTATE=58004. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* none *
****************************************************************
* PROBLEM DESCRIPTION: *
* SQL0901N WHEN QUERY USES MULTIPLE XMLAGG WITH ORDER BY *
* CLAUSE IN THE SAME GROUP BY STATEMENT AND INPUT ARGUMENT NOT *
* NULLABLE. *
****************************************************************
* RECOMMENDATION: *
* none *
**************************************************************** |
| Local Fix: |
There are two local workarounds:
1. Remove NOT NULL WITH DEFAULT clause in the "create table"
statement and no change is required on the query.
CREATE TABLE customer
(cust_id INTEGER NOT NULL WITH DEFAULT
,branch# INTEGER NOT NULL WITH DEFAULT
,balance DECIMAL(12,2) NOT NULL WITH DEFAULT
,firstnme VARCHAR(20)
,middlnme VARCHAR(20)
,lastname VARCHAR(20)
,PRIMARY KEY (cust_id));
COMMIT;
2. Rewrite the XMLAGG in the failing query with maximum ORDER BY
clause.
SELECT branch# AS branch#
,COUNT(*) AS #cust
,MAX(firstnme) AS max_fname
,MAX(middlnme) AS max_mname
,MAX(lastname) AS max_lname
,XMLAGG(XMLTEXT(firstnme || ' ')
ORDER BY firstnme) AS fname2
,XMLAGG(XMLTEXT(middlnme || ' ')) AS mname2
FROM customer
GROUP BY branch#
ORDER BY branch#; |
| available fix packs: |
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
|
| Solution |
Fixed in DB2 v9.5 FP4. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 20.08.2009
01.03.2010
01.03.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.5.FP4
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
