Creating Function Template in UDB 8.2 with TABLE return type for Federation

syntego

Is it possible to create a Function Template in UDB 8.2 with a TABLE
return type?

I would like to have a function federated that has a table return
type.

When I try:

CREATE FUNCTION MFP.MFBESTPRICE_TB(CUSIP VARCHAR(9), EFFDATE DATE)
RETURNS TABLE(DECIMAL(19,9), DATE)
SPECIFIC MFP.MFBESTPRICE_TB
AS TEMPLATE
NOT DETERMINISTIC
NO EXTERNAL ACTION
;

I get the following error:

[IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "(" was
found following "ETURNS TABLE(DECIMAL". Expected tokens may include:
"NUMERIC". LINE NUMBER=3. SQLSTATE=42601

I am able to successfully create a Function Template that returns a
scalar value.

Is this a limit of DB2 8.2, or is there a trick to getting the job
done?

Thanks,

Bob

Jul 19 '07 #1

Serge Rielau

sy*****@gmail.com wrote:

Is it possible to create a Function Template in UDB 8.2 with a TABLE
return type?

I would like to have a function federated that has a table return
type.

When I try:

CREATE FUNCTION MFP.MFBESTPRICE_TB(CUSIP VARCHAR(9), EFFDATE DATE)
RETURNS TABLE(DECIMAL(19,9), DATE)
SPECIFIC MFP.MFBESTPRICE_TB
AS TEMPLATE
NOT DETERMINISTIC
NO EXTERNAL ACTION
;

I get the following error:

[IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "(" was
found following "ETURNS TABLE(DECIMAL". Expected tokens may include:
"NUMERIC". LINE NUMBER=3. SQLSTATE=42601

DB2 is complaining because you don't have names for the columns
(it thinks DECIMAL is the name and now wants a type...)
Try (a DECIMAL(19, 0), b DATE)

I am able to successfully create a Function Template that returns a
scalar value.
Is this a limit of DB2 8.2, or is there a trick to getting the job
done?

I don't know whether it's supported, never tried. But once you fix the
syntax error I'm sure DB2 will tell you...

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Jul 19 '07 #2

syntego

Tried with column names in the return clause:

CREATE FUNCTION MFP.MFBESTPRICE_TB(CUSIP VARCHAR(9), EFFDATE DATE)
RETURNS TABLE(a DECIMAL(19,9), b DATE)
SPECIFIC MFP.MFBESTPRICE_TB
AS TEMPLATE
NOT DETERMINISTIC
NO EXTERNAL ACTION

and received:

[IBM][CLI Driver][DB2/6000] SQL0628N Multiple or conflicting keywords
involving the "RETURNS TABLE" clause are present. LINE NUMBER=3.
SQLSTATE=42613

Another other ideas?

Jul 20 '07 #3