SAP HANA: Table User Defined Functions

 SAP HANA: Table User Defined Functions:

Table User Define Function always returns a table and its parameter may also be off table type.

Table UDFs are read-only user-defined functions which accept multiple input parameters and return exactly one results table.  SQLScript is the only language which is supported by table UDF's. Since these functions are read-only, only read-only statements can be used within the function.  So, you may not use statements like INSERT, UPDATE or DELETE. Also, any procedure calls within the function must also be read-only.  Currently, you can only create these functions in the catalog via the SQL Editor.

Syntax:

CREATE FUNCTION FUNCTION_NAME

(Parameter1 DATA_TYPE,Parameter2 DATA_TYPE)

RETURNS TABLE

( RetruenValue1 DATA_TYPE, ReturnValue2 DATA_TYPE )

BEGIN

RETURN SELECT 1* Parameter1 AS RetruenValue1 ,1*Parameter2 AS ReturnValue2 FROM mytab;

END;

Table User-defined Functions Support the Following:

v  They can have any number of input parameters

v  They return exactly one table

v  Table operations are allowed within the body

v  They are used in the FROM clause of SELECT statements

v  They must be free of side-effects, i.e. DDL statements or the DML statements insert, update and delete cannot be used in function bodies

The SQL statement to define table user-defined functions also is create function. The difference to the definition of a scalar user-defined function is keyword table for the return parameter and how the value of the sole return parameter is assigned using keyword return:

Basic Syntax to Define a Table User-Defined Function

CREATE FUNCTION <function name> (<list of input parameters with type>) RETURNS TABLE [table type| (<list of table column definitions>)] AS BEGIN

<function body>

RETURN <expression to set return table>

END;

              

             A feature that can be helpful in the context of defining table user-defined functions or stored procedures is dynamic filtering using built-in table function apply_filter. This function allows you to apply a dynamic WHERE clause to a database table or table variable and assign the result to a table variable.

Table:

E_ID	E_NAME	OVERTIME(in_Hrs)
1	Kabil	20
2	Nazeer	15
3	Kalai	10
4	Keerthi	10

CODE :

To Create a  Function:

CREATE FUNCTION "KABIL_PRACTICE"."Convert_OfficialsHours"

(im_filter VARCHAR(1000), im_to VARCHAR(1) )

RETURNS TABLE ( "E_ID" NVARCHAR(3),

"E_NAME" NVARCHAR(20),

Overtime DEC (5,2))

AS BEGIN

lt_official = APPLY_FILTER("KABIL_PRACTICE"."OVERTIME"

,:im_filter);

RETURN SELECT "E_ID", "E_NAME",

"KABIL_PRACTICE"."Convert_Hours_IF_ELSE"("OVERTIME(in_Hrs)",:im_to) AS Overtime

FROM :lt_official;

END;

Note:

Apply_filter: You can use APPLY_FILTER with persistent tables and table variables.

The APPLY_FILTER function applies a dynamic filter on a table or table variable. Logically it can be considered a partial dynamic SQL statement. The advantage of the function is that you can assign it to a table variable and will not block SQL – inlining. Despite this, all other disadvantages of a full dynamic SQL yield also for the APPLY_FILTER.

"KABIL_PRACTICE"."Convert_Hours_IF_ELSE": It is a Scalar User Defined function which is already created.

To call a Function:

SELECT * FROM "KABIL_PRACTICE"."Convert_OfficialsHours"('E_NAME LIKE ''%K%''','d');

Result: