Results 1 to 4 of 4

Thread: udf in DB2 v7

  1. #1
    Join Date
    Dec 2004
    Posts
    43

    Unanswered: udf in DB2 v7

    I have a set of UDFs in DB2 v8 for windows
    (for example:
    Code:
    create function lasr_sta.stid2string(sid integer) RETURNS
    varchar(10)
    not deterministic
    reads sql data
    no external action
    language sql
    begin atomic
        declare ret varchar(10);
        if sid in (611,612,613,614)
        then
            set ret = 'АС';
        else
            set ret = 'К';
        end if;
        return ret;
    end
    )
    but in v7 it doesn't work...
    what musT I do to make it work?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    create function lasr_sta.stid2string(sid integer) RETURNS
    varchar(10)
    not deterministic
    reads sql data
    no external action
    language sql
    RETURN CASE WHEN sid in (611,612,613,614) then '??' else '?' end
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Dec 2004
    Posts
    43
    What must I do with this more complexity functions?
    (like this
    Code:
    create function lasr_ar.get_as_path(objId integer, sep varchar(5), suser integer)
    returns varchar(1000)
    not deterministic
    reads sql data
    no external action
    language sql
    begin atomic
        declare lname varchar(255);
        declare path varchar(1000);
        declare pid integer;
    
        set pid = objId;
        set path = '';
    
        while pid is not null
        do
            set (lname, pid) =
            (
            select name, parentId from lasr_ar.A_Systems_all where id=pid
                    and
                    (
                        (wholock is null and isLastEdited = 1)
                        or
                        (wholock is not null and wholock = suser and isLastEdited = 1)
                        or
                        (wholock is not null and wholock <> suser and isPrevEdited = 1)
                    )
            );
            set path = sep || lname || path;
        end while;
        return path;
    end
    or just like this
    Code:
    create function lasr_ar.check_system_id(aid integer)
    returns integer
    not deterministic
    reads sql data
    no external action
    language sql
    return select count(id) from lasr_ar.a_systems_all where id = aid
    )

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Unfortunately, you cannot use Compound statements in Version 7 UDFs.
    (On zOs even V8 does not support compound statements)

    HTH

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

Posting Permissions

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