Facing an odd situation where an SP works in DB2 UDB V7. However, the same procedure fails in V8 with the following error:

SQL0773N The case was not found for the CASE statement. SQLSTATE=20000

Is this a known issue?

Thanks in advance.

From documentation:

SQL0773N Case not found for CASE statement.

Explanation:

A CASE statement without an ELSE clause was found in the routine
body of an SQL routine. None of the conditions specified in the
CASE statement were met.

User Response:

Change the CASE statement to handle all conditions that can
occur.

sqlcode : -773

sqlstate : 20000

Thanks for the input.

However, the issue is that with the same data in the V7 & V8 database, the same SP on V8 fails while it succeeds on V7.

gnanaraj, Can you please post the problem stmt ?

And also, the platform and DB2 Fixpak info

Sathyaram

The platform is Unix.

DB2 information is as found below on V7:
$ db2level
DB21085I Instance "db2inst1" uses DB2 code release "SQL07029" with level
identifier "030A0105" and informational tokens "DB2 v7.1.0.93", "s031208" and
"U495945".

$

DB2 information is as found below on V8:

C:\>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09010" with
level identifier "02010107".
Informational tokens are "DB2 v9.1.0.356", "s060629", "NT32", and Fix Pack "0".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".
C:\>


The error does not occur during CREATE PROCEDURE time. The SQL Stored Procedure is compiled successfully. However, when calling the SP for certain values it does not seem to satisfy the CASE conditions given and fails with the SQL0773N error.

However, the data on both the V7 and V8 database are the same. So also the SQL Stored Procedure is the same code on V7 and V8 database.

However, it succeeds on V7 but fails on V8 with the SQL0773N error.

TIA.

ggnanaraj,
if I understand correctly you are using some kind of CASE statement. Try changing CASE statement that will include ELSE statement.

Something like:
CASE
WHEN.... THEN...
WHEN.... THEN...
...
ELSE some value
END

You can use null instead of some value.

It would be nice to have a create procedure statement.

If you thing it is some kind of bug and you can't solved it by using ELSE statement, then report PMR to IBM.

Hope this helps,
Grofaty

The platform is Unix.


DB2 information is as found below on V8:

C:\>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09010" with
level identifier "02010107".
Informational tokens are "DB2 v9.1.0.356", "s060629", "NT32", and Fix Pack "0".
Product is installed at "C:\PROGRA~1\IBM\SQLLIB" with DB2 Copy Name "DB2COPY1".


Is this correct ?

Also, post the exact case stmt that is causing an error

sathyaram

You also posted this in another newsgroup. You got the same questions there: can you post your CREATE PROCEDURE statement?