Functions allow developers to define a reusable block of data processing logic using the powerful SQLScript language to generate a result as either a single value, or a complete data set. For a modeler, functions are interesting because they can be used in calculation views to provide custom logic when the standard features of graphical calculation view editor are insufficient.
Functions can consume input parameters defined in calculation views. This means that the same function can be reused in different calculation views.
SAP HANA Cloud supports scalar and table functions. Table and scalar functions are created as source files in a project folder. The source file has the extension .hdbfunction The same extension is used for table and scalar functions.
Functions, both scalar and table, are always read-only. This means it is not possible to use any data definition language such as create table or alter table. It is not possible to use data manipulation statements such as update or insert into. Functions never change the database.
Functions can call other functions, which encourages a modular design approach to maximize reuse.
Scalar Function
One of the common uses of a scalar function in a calculation view, is to derive values for input parameters.
A simple example of a scalar function could be to return the current date. However, scalar functions can be more complex and can read tables and call other database objects, including other functions.
A scalar function returns one or more parameters but each parameter always has only a single value. Scalar functions often have one or more input values, but these are not mandatory. For example, returning the current date requires no input parameter, whereas if you wanted to return the date that was x days earlier than today, then you would need to provide x as the input parameter.
The following is a breakdown of a scalar function’s code:
Notice the use of the keyword RETURNS for scalar functions. The keyword RETURNS TABLE is used for table functions. In this example, we are generating two output parameters: RESULT_ADD and RESULT_MUL.
Both output parameters are defined as the Double data type to handle floating point integers.
Table Functions
Table functions are used to return a tabular data set as opposed to a single value parameter. A tabular data set can consist of one or more columns and usually has multiple values (rows). Table functions are commonly used by modelers to define a customized data source in a calculation view. You use a function wherever a data source can be defined, such as in a calculation view Projection node or Join node just as you would include a table. In fact, it might help to think of them as dynamic tables that are created at runtime.
As an example of a table function, consider that you may need to read through a table that contains sales orders so that you can find open orders that have been recently created. The date used for the determination of the required orders is based on an input parameter passed from the calculation view. The resulting open orders are passed to the calculation views as a data source.
The following is a breakdown table function’s code:
Notice the use of the keyword RETURNS TABLE for table functions. Remember, the keyword RETURNS is used for scalar functions.
All output parameters of the table function are offered as input columns to the calculation view node. If your table function requires input parameters you can first define input parameters in the calculation view and then use the input parameter mapping function to map them.
In a calculation view, it is possible to choose a table function as a data source to any node, just as if you were choosing a table. However, in the calculation view editor, there is also a dedicated node: Table Function. This node is specially dedicated to the handling of the different types of mapping of the input parameters of a table function.
There are three types of input mapping available in the Table Function node. They are as follows:
Data Source Column
First, add a data source to the Table Function node. Then, map one of the data source columns to one or more table function input parameters.
Input Parameter
Choose an input parameter from the calculation view and map it to one or more table function input parameters. This could be useful when you want a user to manually enter a value at runtime.
Constant
Manually define a single, fixed value, and map it to one or more table function input parameters. This is really only useful when the input value rarely/never changes.
Note
You should also be aware of Procedures that have many similarities with functions. Just like functions, procedures provide a way to define re-useable, custom SQLScript code for complex data processing. A key advantage of procedures is that they support data definition and data manipulation language that alter the database. However, a modeler rarely needs to alter the database and simply needs to read data. Unlike functions, you cannot use a procedure as a data source in a calculation view. So why should a modeler learn to define procedures? It is because you can define a flexible analytic privilege using SQLScript logic defined in a procedure. See the SAP Help documentation for more details on procedures.