Hi everyone. I've tried to write a function in db2 but it wouldnt compile. I've tried to compile one of the examples from the db2 help, but i get the following error:
During SQL processing it returned:
SQL0104N An unexpected token "''" was found following "archar(4000) default".
Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=6.
The code tested was:
create function tms.REVERSE(INSTR varchar(4000))
deterministic no external action contains sql
-- This function reverses a string.
declare REVSTR, RESTSTR varchar(4000) default '';
declare LEN INT;
if INSTR is NULL then
set (RESTSTR, LEN) = (INSTR, length(INSTR));
while LEN > 0 do
set (REVSTR, RESTSTR, LEN) = (substr(RESTSTR, 1, 1) concat REVSTR, substr(RESTSTR, 2, LEN - 1), LEN - 1);
You have to set the delimiter, whether you work from the control center or not. The easiest way is to make it explicit. Remember: there are two delimiters when creating functions. One terminates lines within the function, the other terminates the function itself.
Example, straight from the DB2 Cookbook:
SET DELIMITER !
CREATE FUNCTION dpt1 (deptin SMALLINT)
DECLARE num_names SMALLINT;
FOR getnames AS
SELECT COUNT(*) AS #n
WHERE dept = deptin
SET num_names = #n;