0

I have to migrate all procedures from Oracle to DB2 database. I'd like to know, how do I do SET syntax inside of IF as Oracle works.

Variable has been using in Oracle is ps_iPkCooperativa and are receiving 0.

Oracle Code :

BEGIN
    SELECT CCOOP
      INTO ps_iPkCooperativa
      FROM COOP
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN  ps_iPkCooperativa := 0;
END;

How do I do this in DB2 ?

Luiz
  • 141
  • 2
  • 14

1 Answers1

0

you can make such a conversion to db2 :

    BEGIN ATOMIC
     DECLARE ps_iPkCooperativa INT;

        SELECT CCOOP
          INTO ps_iPkCooperativa
          FROM COOP;         
     SIGNAL SQLSTATE '02000' -- means no_data_found
          SET MESSAGE_TEXT='No Data Found ';
          SET ps_iPkCooperativa = 0;
    END@

where

The ATOMIC compound statement, as the name suggests, can be thought of as a singular whole—if any unhandled error conditions arise within it, all statements which have been executed up to that point are considered to have failed as well and are therefore rolled back. ATOMIC compound statements cannot be nested inside other ATOMIC compound statements.

In addition, you cannot use SAVEPOINTs or issue explicit COMMITs or ROLLBACKs from within an ATOMIC compound statement.

NOTE

COMMIT, ROLLBACK, SAVEPOINTS and nested ATOMIC compound statements are not allowed within an ATOMIC compound statement.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
  • Barbaros Ozhan , i did query above , but when i try to execute on db2 , show me thats not able to do it . `SELECT CCOOP INTO ps_iPkCooperativa FROM GPRSD001.COOP; SIGNAL SQLSTATE '02000' SET ps_iPkCooperativa = 0;` – Luiz Dec 19 '17 at 10:44
  • Tells me that i have to put semicolon end of '0200' – Luiz Dec 19 '17 at 10:45
  • @Luiz yes, you're right, sorry. I've added `SET MESSAGE_TEXT='No Data Found ';` part. – Barbaros Özhan Dec 19 '17 at 11:11