Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    2

    Question Unanswered: [NewBe] Create function with procedural statement

    Hello to all,
    I'm started to discover Db2 just a week ago, so I'm a very Dummy user !!

    I've got experience on Oracle but DB2 have some rilevant differences!!

    Ok the problem:

    Db2 version 8 (?) on z/Os

    Can I use complex statement in a function like that:

    create function int2Time (intTime integer )
    RETURNS time
    LANGUAGE SQL
    DETERMINISTIC NO EXTERNAL ACTION

    begin atomic
    declare char_time char(6);
    declare char_time_separated char(8)
    char_time := cast ( intTime as char);
    return_time: = substr( char_time, 1, 2 )
    || ':' || substr( char_time, 3, 2 )
    || ':' || substr( char_time, 5, 2 ) ;

    return cast(char_time_separated as time)
    end;

    Thanks!!!

    Bay!

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    To use complex statements(including SQL control statements) in a function,
    you need to upgrade to DB2 version 10 for z/OS.

    But, you can write rather complex expression without using SQL control statements.

    For your sample function,
    you may want to write it on DB2 version 8 for z/OS like this
    (Not tested.)
    Code:
    CREATE FUNCTION int2Time ( int_time INTEGER )
     RETURNS TIME
     LANGUAGE SQL
     DETERMINISTIC
     NO EXTERNAL ACTION
    RETURN
       TIME(          SUBSTR( DIGITS(int_time), 5, 2 )
            || ':' || SUBSTR( DIGITS(int_time), 7, 2 )
            || ':' || SUBSTR( DIGITS(int_time), 9, 2 )
           )
    ;

  3. #3
    Join Date
    Aug 2011
    Posts
    2
    Doh!!!

    I supposed that (terrible sensation!)

    Ok, are there any other way to achive my goal?

    E.g. write a function that calls e procedure, or writing package or some else?

    Well, I wish to prepare a set of tools specific to maintene a specific not well-formed database...

    Thank'you a lot for this help!!!

    Bye!!

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Ok, are there any other way to achive my goal?
    I thought that you can write external functions with language C, Java, so on.

Posting Permissions

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