SEO

permalink · #1 (**permalink**) 02-26-2008, 10:02 AM

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.

permalink · #2 (**permalink**) 02-26-2008, 10:03 AM

Knut Stolze <stolze@de.ibm.com> wrote in message news:<bfikun$2fc$2@fsuj29.rz.uni-jena.de>...
> 3W <trefork@yahoo.com> 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

permalink · #3 (**permalink**) 02-26-2008, 10:03 AM

3W <trefork@yahoo.com> wrote:

> Knut Stolze <stolze@de.ibm.com> wrote in message
> news:<bfikun$2fc$2@fsuj29.rz.uni-jena.de>...
>> 3W <trefork@yahoo.com> 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

permalink · #4 (**permalink**) 02-26-2008, 10:03 AM

Knut Stolze <stolze@de.ibm.com> wrote in message news:<bfjmro$7vs$1@fsuj29.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 :-)

permalink · #5 (**permalink**) 02-26-2008, 10:03 AM

Knut Stolze <stolze@de.ibm.com> wrote in message news:<bfjmro$7vs$1@fsuj29.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 :-)

permalink · #6 (**permalink**) 02-26-2008, 10:03 AM

Knut Stolze <stolze@de.ibm.com> wrote in message news:<bfjmro$7vs$1@fsuj29.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 :-)

permalink · #7 (**permalink**) 02-26-2008, 10:04 AM

tonkuma@jp.ibm.com (Tokunaga T.) wrote in message news:<8156d9ae.0307221910.5a046fbe@posting.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.

permalink · #8 (**permalink**) 02-26-2008, 10:04 AM

trefork@yahoo.com (3W) wrote in message news:<498a2e3c.0307230856.660c8f2@posting.google.c om>...
> tonkuma@jp.ibm.com (Tokunaga T.) wrote in message news:<8156d9ae.0307221910.5a046fbe@posting.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 :-)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

User defined function causes empty result set