| Latest versions | fixlist |
| 11.1.0.7 |
 |
| 10.5.0.9 |
|
| 10.1.0.6 |
|
| 9.8.0.5 |
|
| 9.7.0.11 |
|
| 9.5.0.10 |
|
| 9.1.0.12 |
|
| Have problems? - contact us. | |
| Register for free |
|
| Contact form |
|
DB2 - Problem description
| Problem IC63087 | Status: Closed |
Syntax error while parsing big call statements having multiple escape clauses | |
| product: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
| Problem description: | |
CLI is not parsing the escape clauses correctly for the big call
statements which is having multiple escape clauses.
For example:
From the DB2 Command Window:
db2 "create table abc.test(a int)"
db2 "CREATE PROCEDURE insertDataA( IN a INT) BEGIN INSERT INTO
abc.test VALUES (a); end"
db2 "CALL insertDataA(case when month('10-10-2008') = 4 then
month('10-10-2008') else month ('10-10-2008') - 1 end)"
Return Status = 0
db2 "select * from abc.test"
A
-----------
9
1 record(s) selected.
In the CLI application:
We need to use the escape clauses as below.
"{CALL insertDataA(case when {fn month('10-10-2008')} = 4 then
{fn month('10-10-2008')} else {fn month ('10-10-2008')} - 1
end)}"
However, it failed with the following error message.
SQL0104N An unexpected token "then" was found following
"E('2008-10-10')) = 4". Expected tokens may include: "THEN
<result>". SQLSTATE=42601 | |
| Problem Summary: | |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* CLI is not parsing the escape clauses correctly for the big *
* call *
* statements which is having multiple escape clauses. *
* *
* *
* *
* For example: *
* *
* *
* *
* From the DB2 Command Window: *
* *
* *
* *
* db2 "create table abc.test(a int)" *
* *
* *
* *
* db2 "CREATE PROCEDURE insertDataA( IN a INT) BEGIN INSERT *
* INTO *
* abc.test VALUES (a); end" *
* *
* *
* *
* db2 "CALL insertDataA(case when month('10-10-2008') = 4 then *
* *
* month('10-10-2008') else month ('10-10-2008') - 1 end)" *
* *
* *
* *
* Return Status = 0 *
* *
* *
* *
* db2 "select * from abc.test" *
* *
* *
* *
* A *
* *
* ----------- *
* *
* 9 *
* *
* *
* *
* 1 record(s) selected. *
* *
* *
* *
* *
* *
* In the CLI application: *
* *
* *
* *
* We need to use the escape clauses as below. *
* *
* *
* *
* "{CALL insertDataA(case when {fn month('10-10-2008')} = 4 *
* then *
* {fn month('10-10-2008')} else {fn month ('10-10-2008')} - 1 *
* *
* end)}" *
* *
* *
* *
* However, it failed with the following error message. *
* *
* *
* *
* SQL0104N An unexpected token "then" was found following *
* *
* "E('2008-10-10')) = 4". Expected tokens may include: "THEN *
* *
* <result>". SQLSTATE=42601 *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 1. *
**************************************************************** | |
| Local Fix: | |
n/a | |
| available fix packs: | |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows | |
| Solution | |
Problem was first fixed in Version 9.7 Fix Pack 1. | |
| Workaround | |
not known / see Local fix | |
| Timestamps | |
Date - problem reported : Date - problem closed : Date - last modified : | 11.09.2009 25.01.2010 25.01.2010 |
| Problem solved at the following versions (IBM BugInfos) | |
9.7.FP1 | |
| Problem solved according to the fixlist(s) of the following version(s) | |
| 9.7.0.1 |
|