By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,683 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.