DB2 CASE/IF condition control statement

IF statement format (only used in DB2 processes, triggers, and functions)
IF (condition)
THEN statement;
ELSEIF (condition)
THEN statement;
ELSEIF (condition)
THEN statement;
......
ELSE
Statement;
End if;
 
Example:
[SQL]
BEGIN
-- Retrieve the maximum ID
SET NEW_ID = NULL;
-- (Select top 1 my_id from tempvoucherno order by my_id DESC)
SET NEW_ID = (SELECT my_id FROM "PIGGY". tempvoucherno fetch first 1 rows only );
-- If not
IF NEW_ID IS NULL
Then set NEW_ID = '123 ';
ELSE
BEGIN
Declare num varchar (7 );
-- Retrieve the number + 1 CONVERT (VARCHAR, (CONVERT (INTEGER, RIGHT (NEW_ID, 7) + 1 ));
-- CAST (RIGHT (NEW_ID, 7) as INTEGER) + 1) as VARCHAR (7 ));
-- Set num = CONVERT (VARCHAR (7), (CONVERT (INTEGER, RIGHT (NEW_ID, 7) + 1 ));

-- Set num = REPLICATE ('0', 7-LEN (NUM) + NUM;

SET NEW_ID = NUM;
END;
End if;
Insert into "PIGGY". TEMPVOUCHERNO (my_id) values (NEW_ID );
 
END


Author cuiran