UDF Return-Table Based On Repository Table

[Former Member]

I have tried the following:

FUNCTION "mySchema"."myFunction" ( )

  RETURNS TABLE ( "SAPR3"."TCURR" )

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER AS

BEGIN

/*****************************

  Write your function logic

*****************************/

END;

and various derivations thereof, but activation fails with the syntax error "Syntax error in table function object: incorrect syntax near ".": line 13 col 26 (at pos 475)" and I cannot find anything which works.

Any suggestions out there?

[pfefferf · Accepted solution]

There are different problems in your coding:

• You are using "TABLE" for RETURNS. With that you indicate that you wanna define your own type. But you are referencing to an existing catalog object.
• "SAPR3"."TCURR" is a catalog object and cannot be used as type at that place. You have to declare a table type (either in catalog or via CDS) or do an inline declaration using "TABLE".
• Finally you have to provide a body which returns data. W/o you cannot activate the table function.

Here is an example using the inline type declaration + returning 3 fields of the table:

[Former Member · Accepted solution]

Thanks for your answer.

I know the 'inline' and' CDS' options, but what do you mean with "in catalog"?

[pfefferf · Accepted solution]

"SAPR3"."TCURR" is a catalog object which has not representation via a design-time object in the repository.

[Former Member · Accepted solution]

Sorry, my questions was not clear enough.

In your first answer you wrote: "You have to declare a table type (either in catalog or via CDS)"

What do you mean with "in catalog" in that sentence?

[pfefferf · Accepted solution]

Ah, ok. I meant a table type created in the catalog via CREATE TYPE. But of course the CDS variant in combination with a .hdbtablefunction is the preferred way because of its advantages (e.g. transportable).

[Former Member · Accepted solution]

This message was moderated.