Prev: DB2 UDF Problem
Next: 5 Reasons why In-memory analysis matters
From: Massimiliano Campagnoli on 15 Jun 2010 16:49
Hello,
I need to develop an UDF which return three scalar values.
How can I group these three scalars together ?
I've tried

CREATE FUNCTION MYFUNC (P INT)
RETURNS ROW (I1 INT, I2 INT, I3 INT)
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
....

RETURN VALUES (v1, v2 , v3)

but how can I invoke it and use it in a select list ?

SELECT MYFUNC(1) from ...

does not work.

Can you please explain me how can I invoke this function ?
From: Lennart Jonsson on 15 Jun 2010 17:07
On 2010-06-15 22:49, Massimiliano Campagnoli wrote:
> Hello,
> I need to develop an UDF which return three scalar values.
> How can I group these three scalars together ?
> I've tried
>
> CREATE FUNCTION MYFUNC (P INT)
> RETURNS ROW (I1 INT, I2 INT, I3 INT)
> LANGUAGE SQL
> CONTAINS SQL
> NO EXTERNAL ACTION
> DETERMINISTIC
> ....
>
> RETURN VALUES (v1, v2 , v3)
>
> but how can I invoke it and use it in a select list ?
>
> SELECT MYFUNC(1) from ...
>
> does not work.
>
> Can you please explain me how can I invoke this function ?

First (you don't mention neither platform nor version, so I'll assume
LUW 9.5):

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=/com.ibm.db2.luw.sql.ref.doc/doc/r0004240.html

ROW column-list
Specifies that the output of the function is a single row. If the
function returns more than one row, an error is raised (SQLSTATE 21505).
The column-list must include at least two columns (SQLSTATE 428F0).

A row function can only be used as a transform function for a
structured type (having one structured type as its parameter and
returning only base types).

So perhaps it is a table function you need? You create one like:

CREATE FUNCTION MYFUNC (P INT)
RETURNS TABLE (I1 INT, I2 INT, I3 INT)
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
.....
RETURN VALUES (v1, v2, v3)

and you invoke it with:

select * from table(myfunc(...))
From: Massimiliano Campagnoli on 16 Jun 2010 14:43
On 15 Giu, 23:07, Lennart Jonsson <erik.lennart.jons...(a)gmail.com>
wrote:
> On 2010-06-15 22:49, Massimiliano Campagnoli wrote:
>
>
>
>
>
> > Hello,
> > I need to develop an UDF which return three scalar values.
> > How can I group these three scalars together ?
> > I've tried
>
> >  CREATE FUNCTION MYFUNC (P INT)
> >      RETURNS ROW (I1 INT, I2 INT, I3 INT)
> >      LANGUAGE SQL
> >      CONTAINS SQL
> >      NO EXTERNAL ACTION
> >      DETERMINISTIC
> >       ....
>
> >      RETURN VALUES (v1, v2 , v3)
>
> > but how can I invoke it and use it in a select list ?
>
> > SELECT MYFUNC(1) from ...
>
> > does not work.
>
> > Can you please explain me how can I invoke this function ?
>
> First (you don't mention neither platform nor version, so I'll assume
> LUW 9.5):
>
> http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=....
>
> ROW column-list
>     Specifies that the output of the function is a single row. If the
> function returns more than one row, an error is raised (SQLSTATE 21505).
> The column-list must include at least two columns (SQLSTATE 428F0).
>
>     A row function can only be used as a transform function for a
> structured type (having one structured type as its parameter and
> returning only base types).
>
> So perhaps it is a table function you need? You create one like:
>
> CREATE FUNCTION MYFUNC (P INT)
> RETURNS TABLE (I1 INT, I2 INT, I3 INT)
> LANGUAGE SQL
> CONTAINS SQL
> NO EXTERNAL ACTION
> DETERMINISTIC
> ....
> RETURN VALUES (v1, v2, v3)
>
> and you invoke it with:
>
> select * from table(myfunc(...))

thanks,
so you invoke in the same way, using same syntax, a function returning
a single row and a function returning a full table.
From: Lennart Jonsson on 16 Jun 2010 15:25
On 2010-06-16 20:43, Massimiliano Campagnoli wrote:
> On 15 Giu, 23:07, Lennart Jonsson <erik.lennart.jons...(a)gmail.com>
> wrote:
>> On 2010-06-15 22:49, Massimiliano Campagnoli wrote:
>>
>>
>>
>>
>>
>> > Hello,
>> > I need to develop an UDF which return three scalar values.
>> > How can I group these three scalars together ?
>> > I've tried
>>
>> > CREATE FUNCTION MYFUNC (P INT)
>> > RETURNS ROW (I1 INT, I2 INT, I3 INT)
>> > LANGUAGE SQL
>> > CONTAINS SQL
>> > NO EXTERNAL ACTION
>> > DETERMINISTIC
>> > ....
>>
>> > RETURN VALUES (v1, v2 , v3)
>>
>> > but how can I invoke it and use it in a select list ?
>>
>> > SELECT MYFUNC(1) from ...
>>
>> > does not work.
>>
>> > Can you please explain me how can I invoke this function ?
>>
>> First (you don't mention neither platform nor version, so I'll assume
>> LUW 9.5):
>>
>> http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/index.jsp?topic=...
>>
>> ROW column-list
>> Specifies that the output of the function is a single row. If the
>> function returns more than one row, an error is raised (SQLSTATE 21505).
>> The column-list must include at least two columns (SQLSTATE 428F0).
>>
>> A row function can only be used as a transform function for a
>> structured type (having one structured type as its parameter and
>> returning only base types).
>>
>> So perhaps it is a table function you need? You create one like:
>>
>> CREATE FUNCTION MYFUNC (P INT)
>> RETURNS TABLE (I1 INT, I2 INT, I3 INT)
>> LANGUAGE SQL
>> CONTAINS SQL
>> NO EXTERNAL ACTION
>> DETERMINISTIC
>> ....
>> RETURN VALUES (v1, v2, v3)
>>
>> and you invoke it with:
>>
>> select * from table(myfunc(...))
>
> thanks,
> so you invoke in the same way, using same syntax, a function returning
> a single row and a function returning a full table.

Yes, a table with one row. Example:

[...]$ db2 -v -td@ -f f.sql
drop function myfun
DB20000I The SQL command completed successfully.

create function myfun (p int)
returns table( i1 int, i2 int, i3 int )
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC

return values (p, p*p, p*p*p)


DB20000I The SQL command completed successfully.

[...]$ db2 "select * from table(myfun(3))"

I1 I2 I3
----------- ----------- -----------
3 9 27

1 record(s) selected.


 | 
Pages: 1
Prev: DB2 UDF Problem
Next: 5 Reasons why In-memory analysis matters