467,887 Members | 1,568 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,887 developers. It's quick & easy.

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

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
  • viewed: 1857
Share:
2 Replies
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
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.

Similar topics

31 posts views Thread by nikola | last post: by
6 posts views Thread by Jef Driesen | last post: by
8 posts views Thread by vpadial | last post: by
6 posts views Thread by BMax | last post: by
3 posts views Thread by alan | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.