Professional Web Applications Themes

User defined function causes empty result set - IBM DB2

I have created a UDF that takes an INTEGER as parameter and returns a VARCHAR. Whenever I try to use the function in a SELECT, it causes the SELECT to return an empty result set. I'm on V7....

  1. #1

    Default User defined function causes empty result set

    I have created a UDF that takes an INTEGER as parameter and returns a
    VARCHAR. Whenever I try to use the function in a SELECT, it causes the
    SELECT to return an empty result set.
    I'm on V7.
    3W Guest

  2. #2

    Default Re: User defined function causes empty result set

    Knut Stolze <stolzede.ibm.com> wrote in message news:<bfikun$2fc$2fsuj29.rz.uni-jena.de>...
    > 3W <trefork> wrote:
    >
    > > I have created a UDF that takes an INTEGER as parameter and returns a
    > > VARCHAR. Whenever I try to use the function in a SELECT, it causes the
    > > SELECT to return an empty result set.
    >
    > How does your function look like (code)?
    > How was it registered in the database (CREATE FUNCTION)?
    > How do you try to call it?
    It was created by running the CREATE FUNCTION in the Command Center. I
    have verified that it was created, by using Control
    Center->Database->Application Objects->User defined functions. It's
    there, looking correct.

    My select statement:
    select isLeapYear(1976) from SomeTable

    The code:

    create function isLeapYear(testYear integer)
    returns varchar(5)
    language sql
    deterministic
    no external action
    begin atomic
    if ( mod(testYear, 4) = 0 ) then
    if ( mod(testYear, 100) = 0 ) then
    if ( mod(testYear, 400) = 0 ) then
    return varchar('TRUE');
    else
    return varchar('FALSE');
    end if;
    else
    return varchar('TRUE');
    end if;
    else
    return varchar('FALSE');
    end if;
    end
    3W Guest

  3. #3

    Default Re: User defined function causes empty result set

    3W <trefork> wrote:
    > Knut Stolze <stolzede.ibm.com> wrote in message
    > news:<bfikun$2fc$2fsuj29.rz.uni-jena.de>...
    >> 3W <trefork> wrote:
    >>
    >> > I have created a UDF that takes an INTEGER as parameter and returns a
    >> > VARCHAR. Whenever I try to use the function in a SELECT, it causes the
    >> > SELECT to return an empty result set.
    >>
    >> How does your function look like (code)?
    >> How was it registered in the database (CREATE FUNCTION)?
    >> How do you try to call it?
    >
    > It was created by running the CREATE FUNCTION in the Command Center. I
    > have verified that it was created, by using Control
    > Center->Database->Application Objects->User defined functions. It's
    > there, looking correct.
    >
    > My select statement:
    > select isLeapYear(1976) from SomeTable
    >
    > The code:
    >
    > create function isLeapYear(testYear integer)
    > returns varchar(5)
    > language sql
    > deterministic
    > no external action
    > begin atomic
    > if ( mod(testYear, 4) = 0 ) then
    > if ( mod(testYear, 100) = 0 ) then
    > if ( mod(testYear, 400) = 0 ) then
    > return varchar('TRUE');
    > else
    > return varchar('FALSE');
    > end if;
    > else
    > return varchar('TRUE');
    > end if;
    > else
    > return varchar('FALSE');
    > end if;
    > end
    Are you sure that there is some data in "SomeTable"? What does "SELECT
    COUNT(*) FROM SomeTable" return?

    I am asking because your function works quite nicely for me:

    db2 => values isleapyear(1976)

    1
    -----
    TRUE

    1 record(s) selected.

    db2 => select isleapyear(2000) from sysibm.sysdummy1

    1
    -----
    TRUE

    1 record(s) selected.


    p.s: I think you can simplify the function a bit:

    CREATE FUNCTION isLeapYear(testYear INTEGER)
    RETURNS VARCHAR(5)
    LANGUAGE SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    CONTAINS SQL
    RETURN CASE
    WHEN MOD(testYear, 4) = 0 AND
    ( MOD(testYear, 100) <> 0 OR
    MOD(testYear, 400) = 0 )
    THEN 'TRUE'
    ELSE 'FALSE'
    END


    --
    Knut Stolze
    Information Integration
    IBM Germany / University of Jena
    Knut Stolze Guest

  4. #4

    Default Re: User defined function causes empty result set

    Knut Stolze <stolzede.ibm.com> wrote in message news:<bfjmro$7vs$1fsuj29.rz.uni-jena.de>...
    > Are you sure that there is some data in "SomeTable"? What does "SELECT
    > COUNT(*) FROM SomeTable" return?
    I figured it out. For some odd reason DB2 on NT (at least V7) requires
    that your function ends with a RETURN statement. It will not complain,
    it just won't work. I tried the same function on DB2/400 without
    problems.
    Nice consistency IBM :-)
    3W Guest

  5. #5

    Default Re: User defined function causes empty result set

    Knut Stolze <stolzede.ibm.com> wrote in message news:<bfjmro$7vs$1fsuj29.rz.uni-jena.de>...
    > Are you sure that there is some data in "SomeTable"? What does "SELECT
    > COUNT(*) FROM SomeTable" return?
    I figured it out. For some odd reason DB2 on NT (at least V7) requires
    that your function ends with a RETURN statement. It will not complain,
    it just won't work. I tried the same function on DB2/400 without
    problems.
    Nice consistency IBM :-)
    3W Guest

  6. #6

    Default Re: User defined function causes empty result set

    Knut Stolze <stolzede.ibm.com> wrote in message news:<bfjmro$7vs$1fsuj29.rz.uni-jena.de>...
    > Are you sure that there is some data in "SomeTable"? What does "SELECT
    > COUNT(*) FROM SomeTable" return?
    I figured it out. For some odd reason DB2 on NT (at least V7) requires
    that your function ends with a RETURN statement. It will not complain,
    it just won't work. I tried the same function on DB2/400 without
    problems.
    Nice consistency IBM :-)
    3W Guest

  7. #7

    Default Re: User defined function causes empty result set

    [email]tonkumajp.ibm.com[/email] (Tokunaga T.) wrote in message news:<8156d9ae.0307221910.5a046fbeposting.google. com>...
    > Another idea for isLeapYear:
    > CREATE FUNCTION isLeapYear(testYear integer)
    > returns varchar(5)
    > language sql
    > deterministic
    > no external action
    > RETURN
    > CASE
    > WHEN DAY( DATE(RTRIM(CHAR(testYear)) || '-03-01') - 1 DAY ) > 28 THEN 'TRUE'
    > ELSE 'FALSE'
    > END
    >
    > No need to know how to calculate leap year.
    Except that your suggestion has a big performance impact, compared to
    Knut's suggestion, which I prefer for that reason.
    3W Guest

  8. #8

    Default Re: User defined function causes empty result set

    [email]trefork[/email] (3W) wrote in message news:<498a2e3c.0307230856.660c8f2posting.google.c om>...
    > [email]tonkumajp.ibm.com[/email] (Tokunaga T.) wrote in message news:<8156d9ae.0307221910.5a046fbeposting.google. com>...
    > > Another idea for isLeapYear:
    > > CREATE FUNCTION isLeapYear(testYear integer)
    > > returns varchar(5)
    > > language sql
    > > deterministic
    > > no external action
    > > RETURN
    > > CASE
    > > WHEN DAY( DATE(RTRIM(CHAR(testYear)) || '-03-01') - 1 DAY ) > 28 THEN 'TRUE'
    > > ELSE 'FALSE'
    > > END
    > >
    > > No need to know how to calculate leap year.
    >
    > Except that your suggestion has a big performance impact, compared to
    > Knut's suggestion, which I prefer for that reason.
    Plus it'll fail for years less than 1000. That may no be a problem,
    but then why not just divide by 4. That'll be good between 1901-2099.
    BTW, I'm not suggesting this :-)
    3W Guest

Similar Threads

  1. How to get parameter names from defined function
    By DutchFish in forum PHP Development
    Replies: 5
    Last Post: February 8th, 11:40 PM
  2. Defined result if
    By John in forum FileMaker
    Replies: 7
    Last Post: October 27th, 06:04 AM
  3. 2 horizontal column, but with odd number of result = empty cell!!
    By Jay in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: August 28th, 07:47 PM
  4. [PHP] user-defined superglobals?
    By Ray Hunter in forum PHP Development
    Replies: 5
    Last Post: August 24th, 01:37 AM
  5. user defined cursor from a sp
    By Arun[symbiosis] in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 3rd, 08:58 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139