Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184

    Unanswered: Stored Procedure works in V7 but fails in V8

    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.

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    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

  3. #3
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    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.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    gnanaraj, Can you please post the problem stmt ?

    And also, the platform and DB2 Fixpak info

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Quote Originally Posted by sathyaram_s
    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.

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    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

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You also posted this in another newsgroup. You got the same questions there: can you post your CREATE PROCEDURE statement?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •