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 |