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 ?

On 2010-06-15 22:49, Massimiliano Campagnoli wrote:
First (you don't mention neither platform nor version, so I'll assume
LUW 9.5):

http://publib.boulder.ibm.com/infoce.../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(...))

On 15 Giu, 23:07, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
thanks,
so you invoke in the same way, using same syntax, a function returning
a single row and a function returning a full table.

On 2010-06-16 20:43, Massimiliano Campagnoli wrote:
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.