Table Functions are now the recommended
script-based artefact whenever a modelling requirement cannot be fulfilled with
graphical information views.
In previous
versions of SAP HANA, it was possible to create scripted calculation views,
which are similar in their key principles but provide less flexibility and
performance. In particular, Table User Defined Functions allow an improved
movement of filters from client tools down to the source data.
You create design-time Table Functions with the SAP
HANA Developer perspective.
It is also
possible to convert existing Scripted Calculation
Views into Table Functions, with a migration tool available in SAP HANA
Studio since SAP HANA SPS11.
Note:
Table functions can return only a
single result set.
Table
functions are read-only functions. This means
that you cannot use insert, update, delete, commit,
or exec SQL statement inside a table function. If you have any of these
SQL statements in a table function, it will not
activate.
Table Functions
•
are side-effect free read-only functions that use standard SQL
•
can be used as source data in Graphical Calculation Views
•
consume data from tables, models, functions, predictive algorithms, etc.
EXAMPLES:
To start, as always, make sure
you are in the Development Perspective.
Step 1: From the Developer Perspective, choose Repositories tab.
Step 2: Select the package where
you want to create a Table Function, Right-click the
package selects New and choose Other.
Step 3: This brings up the new window and
select Table Function and click Next.
Step 4: Now provide a name to this table function. Here I named it
as a TBL_FN_DEMO click anywhere outside the
box .hdbtablefunction will automatically
appended to the name and Click Finish.
Step 5: This opens up a table function with some default code
already present and makes up the skeleton of the overall code which is always
needed.
Firstly, let’s clean up the
comments placed by the system. Remove them by deleting the text between BEGIN and END block.
Step 6: Let’s understand these rows one by one. The FUNCTION keyword marks the beginning of a SAP HANA function.
"USER2"."KABIL_PRACTICE::TBL_FN_DEMO"
( )
This keyword is followed by the
auto-generated complete name of the table function which is a combination of
the
USER2 = username
KABIL_PRACTICE
= Package Name
TBL_FN_DEMO =
Table Function Name
The set of open and closed
brackets “( )” at the end is used to add any
Input parameters to this table function. For
now, we will leave it blank inside.
RETURNS
return_table_type
The next line starts with a RETURNS keyword. The return_table_type
is just a placeholder for the actual code. This is similar to the
columns folder in the Scripted view where
you specify the list of output fields for the Scripted node.
We need to remove this return_table_type and provide the structure and
datatype of the output we expect from this table function.
As seen below, we add the keyword
TABLE to specify that the output of this
function is a table and it will have 4 fields
Below the BEGIN Keyword we have to write RETURN
Keyword. This tells HANA that the select statement you write below this would be the
output of this table function.
Step 7: Now, we are required to write the select
statement which provides output in the same
structure and order as the RETURNS TABLE statement.
Now, write your logic below the RETURN statement as shown as below:
CODE:
FUNCTION "USER2"."KABIL_PRACTICE::TBL_FN_DEMO" ( )
RETURNS Table(
"Order_ID" Decimal(8,1),
"Order_Date" Date,
"Order_Quantity" Decimal(5,1),
"Sales" Decimal(31,13)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN
RETURN
select "Order_ID",
"Order_Date",
"Order_Quantity",
"Sales"
from "KABIL_PRACTICE"."SUPERSTORE_SALES";
END;
Step 8: Finally validate and activate the Table Function.
you can now notice that an active
table function is now available in your
package.
Step 9: To check the data consistency of this table function,
select Modeler Perspective, from the SQL Console run a below query:
select * from "USER2"."KABIL_PRACTICE::TBL_FN_DEMO" ( ) ;
Result:
Thank you for visitng My Blog....
Share Your Comments
