I can't get the syntax for a table defined function to work.

[Former Member]

Specifically I'm trying to use a table type definition in my function.

CREATE TYPE "MYSCHEMA"."TT_MYTABLE" AS TABLE (

     "Field" varchar(12)

)

CREATE FUNCTION "MYSCHEMA"."MYFUNCTION"()

RETURNS TABLE ("MYSCHEMA"."TT_MYTABLE")

LANGUAGE SQLSCRIPT

...

All the function examples use an actual field definitions and not an predefined table type.  I've tried the above and a bunch of other variations but I get a syntax error on all of them.

This is the documentation page;

http://help.sap.com/saphelp_hanaplatform/helpdata/en/20/d42e77751910149f0ff6b879b1290f/content.htm

They don't show any examples using the table types and I can't seem to make sense of " <datatype> ::= <sql_type> | <table_type>".

[former_member184871 · Accepted solution]

Hi Joe,

Refer

and

Hope it helps

Regards

Kumar

[Former Member · Accepted solution]

Hi Kumar,

Neither one of those pages shows how to use a table type definition.  I'm going to be creating a number of functions that return the same table definition. That's why I want to use a table type instead of actually defining the table in each function. That keeps the table definition on one place for all the functions that use it.

Joe Goldthwaite.

[sagarjoshi · Accepted solution]

If you read the grammar correctly then I think it is not possible. Table UDF does not allow return table type that refers to another structure type definition but only elementary sql_type

I think what grammar you refering is from input parameters

Table Functions must return a table whose type is defined by <return_table_type>. And Scalar Funcitons must return scalar values specified in <return_parameter_list>.

Defines the output parameters

 <return_table_type> ::= TABLE ( <ret_column_list> ) 

Defines the structure of the returned table data.

 <ret_column_list> ::= <ret_column_elem>[{, <ret_column_elem>}...] 

Defines the list of columns returned from the function.

 <ret_column_elem> ::= <column_name> <sql_type> 
<column_name> ::= <identifier>

[Former Member · Accepted solution]

Thanks Sagar.  I appreciate the input.  It turns out you can do it and I was able to figure it out. You just leave off the TABLE and declaration like this:

     CREATE FUNCTION MY_SCHEMA.MY_FUNCTION()

         RETURNS MY_SCHEMA.TT_MY_TABLE_TYPE

Where TT_MY_TABLE_TYPE is a predefined table type.  It works like a charm.

[sagarjoshi · Accepted solution]

Good to know that this syntax works. Somehow I always thought that TABLE () is mandatory for Table UDF

[Former Member · Accepted solution]

Me too! That's why I had so much trouble figuring out the syntax. I spent hours on it. This learning curve really wears you down.