Results 1 to 5 of 5

Thread: db2 functions

  1. #1
    Join Date
    Jul 2005
    Posts
    7

    Unanswered: db2 functions

    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.
    SQLSTATE=42601
    Any ideas??...

    The code tested was:
    Code:
    create function tms.REVERSE(INSTR varchar(4000))
    returns varchar(4000)
    specific REVERSE
    deterministic no external action contains sql
    -- This function reverses a string.  
    begin atomic
    declare REVSTR, RESTSTR varchar(4000) default '';
    declare LEN INT;
    if INSTR is NULL then
    return NULL;
    end if;
      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);
       end while;
      return REVSTR;
    end

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    In the SQL interface you run this, is ';' the default delimiter for SQL Statements ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2005
    Posts
    7
    I am not what is the delimiter, but it seems its not ';'. Iam using db2 control center. anybody knows whats the delimiter for cc?
    thanks

  4. #4
    Join Date
    Oct 2005
    Posts
    109
    In control center, there is a tab: tools -> settings, there you can change the default delimiter.

    AFAIK the default is of course ';' - you will need to change that for your statement to run.
    Juliane

  5. #5
    Join Date
    Feb 2006
    Location
    Utrecht, Netherlands
    Posts
    16
    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)
    RETURNS SMALLINT
    BEGIN ATOMIC
    DECLARE num_names SMALLINT;
    FOR getnames AS
    SELECT COUNT(*) AS #n
    FROM staff
    WHERE dept = deptin
    DO
    SET num_names = #n;
    END FOR;
    RETURN num_names;
    END!
    COMMIT!

    Should work.
    cheers,
    Rob.

Posting Permissions

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