|
Problem IC73317
|
Status: Closed |
ASSIGNMENT OF PREDICATE TO BOOLEAN VARIABLE INSIDE OF AN IF-STATEMENT CAUSES SYNTAX ERROR. |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
The assignment of a predicate to a boolean variable may result
in a syntax error, SQL0104N, when all of the following
conditions
are met:
- An SQL procedure, compiled SQL function, or compiled trigger
contains an IF statement;
- Each THEN, ELSE IF, or ELSE clause in the IF statement
contains an assignment to the same Boolean variable, and no
other
statements;
- The right-hand side of at least one of the assignment
statements is a predicate.
Example (PL/SQL, DB2_COMPATIBILITY_VECTOR=ORA):
declare
b boolean;
m varchar2(1);
begin
IF m = 'A' THEN
b := m = 'A';
ELSE
b := false;
end if;
end@
which results in an error similar to the following:
SQL0104N An unexpected token "=" was found following "N
:HV00009 :HI00009".
Expected tokens may include: "CONCAT". LINE NUMBER=1.
SQLSTATE=42601
The equivalent DB2 SQL PL example is:
begin
declare b boolean;
declare m varchar(1);
if m = 'A' then
set b = m = 'A';
end if;
end@ |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* The assignment of a predicate to a boolean variable may *
* result *
* in a syntax error, SQL0104N, when all of the following *
* *
* conditions *
* *
* are met: *
* *
* *
* *
* - An SQL procedure, compiled SQL function, or compiled *
* trigger *
* contains an IF statement; *
* *
* - Each THEN, ELSE IF, or ELSE clause in the IF statement *
* *
* contains an assignment to the same Boolean variable, and no *
* *
* other *
* *
* statements; *
* *
* - The right-hand side of at least one of the assignment *
* *
* statements is a predicate. *
* *
* *
* *
* Example (PL/SQL, DB2_COMPATIBILITY_VECTOR=ORA): *
* *
* *
* *
* declare *
* *
* b boolean; *
* *
* m varchar2(1); *
* *
* begin *
* *
* IF m = 'A' THEN *
* *
* b := m = 'A'; *
* *
* ELSE *
* *
* b := false; *
* *
* end if; *
* *
* end@ *
* *
* *
* *
* which results in an error similar to the following: *
* *
* *
* *
* SQL0104N An unexpected token "=" was found following "N *
* *
* :HV00009 :HI00009". *
* *
* *
* *
* Expected tokens may include: "CONCAT". LINE NUMBER=1. *
* *
* SQLSTATE=42601 *
* *
* *
* *
* The equivalent DB2 SQL PL example is: *
* *
* *
* *
* begin *
* *
* declare b boolean; *
* *
* declare m varchar(1); *
* *
* *
* *
* if m = 'A' then *
* *
* set b = m = 'A'; *
* *
* end if; *
* *
* end@ *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 Fix Pack 5. *
**************************************************************** |
| Local Fix: |
As a workaround, if you have an assignment with a boolean-valued
expression inside an IF statement, insert a NULL statement
(PL/SQL) or an empty compound statement (SQL PL) immediately
before or after the assignment. For the PL/SQL case given in
the
error description:
declare
b boolean;
m varchar2(1);
begin
IF m = 'A' THEN
null; -- workaround
b := m = 'A';
end if;
end@
In DB2 SQL PL, the equivalent workaround is:
begin
declare b boolean;
declare m varchar(1);
if m = 'A' then
begin end; -- workaround
set b = m = 'A';
end if;
end@ |
| available fix packs: |
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 |
This problem will be fixed in DB2 Version 9.7 Fix Pack 5. |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 16.12.2010
07.12.2011
07.12.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
