Day 15:

---------
Functions and Procedures:
=====================
A function is defined as the DB objects that accepts the parameters and performs an
action.

A Procedure is a group of statements that you can call by name that is it is a unit
or module that performs the material task. The body of the procedure consists of
sequence of statements separated by the semi colon.
There are tthree ways to create a procedure in SAP HANA:
1. Using SAP HANA XS Project in SAP HANA Development perspective.

Step 1:Log on to SAP HANA Studio

Step 2: Click on Open Perspective
Step 3: Right click on Package->New->XS project
By using this XS project, you can create procedure in SAP HANA Development
perspective. This can be done in SAP HANA Development perspective.
2. Modeler in the SAP HANA Modeler perspective.
Step 1:Log on to SAP HANA Studio
Step 2: Click on Open Perspective
Step 3: Go to SAP HANA perspective
Step 4: Click on SAP HANA Modeler
Step 5: Expand your server HDB
Step 6: Expand content folder
Step 7: Right click on your package
Step 8: Select New-> Procedure

3. Using the SQL Editor

Step 1:Log on to SAP HANA Studio
Step 2: Click on Open SQL Console
Step 3: Here we will be using the SQL Script.

Difference between Function and Procedure:

1. Function must return a value.
In procedure its optional.

2. Function can be called from procedure.

Procedures cannot be called from function.

3. Function can have only one input parameters.

Procedures can have input or output parameters.

Suppose we have procedures PRODUCT_MASTER_PROC, PRODUCT_ITEM_PROC, SALES_PROC.

Step 1: Log on to the SAP HANA

Step 2: Select your server
Step 3: Select Catalog on your server
Step 4: Expand your catalog
Step 5: Expand Tables in catalog
Step 6: Filter your required tables after right clicking on your catalog folder

TABLETYPE:
It is a database table. We can't insert or update or delete in the tabletype. At
run times, only structure is used. In tabletypes, we define parameters for the
procedure that refers the table.

Step 1: Log into the SAP HANA studio.

Step 2: Go to the SQL Console
create type TT_SALE_PRODUCT_INVENTORY as table
(
S_ID integer primary key,
P_ID integer,
P_NAME nvarchar(33),
QTY integer,
SALES_AMOUNT decimal(34, 2)
)

create procedure "PROC_SALE_INVENTORY"

(
IN "P_ID" integer,
IN "P_NAME" nvarchar(32),
IN "QTY" integer,
OUT Result "TT_SALE_PRODUCT_INVENTORY"
)
language SQLSCRIPT
as begin
declare PROD_ID INTEGER;
DECLARE INV INTEGER;
DECLARE EP DECIMAL(34,2);
DECLARE SALES_AMOUNT DECIMAL(34,2);
SELECT COUNT(*) INTO PROD_ID FROM "PRODUCT_ITEM_PROC" WHERE "P_ID" =:P_ID;
SELECT "STOCK" INTO INV FROM "PRODUCT_MASTER_PROC" WHERE "P_ID" = :P_ID;
SELECT "USP" INTO EP FROM "PRODUCT_ITEM_PROC" WHERE "P_ID" = :P_ID;

IF INV >= :QTY THEN

SALES_AMOUNT:= (:QTY*:EP);
INSERT INTO "SALES_PROC" ("P_ID","P_NAME", "QTY","SALES_AMOUNT") VALUES (:P_ID,
:P_NAME, :QTY, :SALES_AMOUNT);
UPDATE "PRODUCT_MASTER_PROC" SET "STOCK" = "STOCK" - :QTY WHERE "P_ID" = :P_ID;
END IF;
RESULT = SELECT * FROM "SALES_PROC";
END;

Example:
P_ID = 5
P_NAME = FAN
Stock = 140
QTY = 10

To call the procedure, use the CALL keyword.

CALL "PROC_SALE_INVENTORY"(5, 'FAN', 10, ?);

Same can be executed by including function in procedure.

Create function "Sales_amount"

(
Q integer,
USP integer
)
Returns result decimal(34,2)
language SQLSCRIPT
SQL Security Invoker As
Begin
result := :Q * :USP;

End;
Step 3: Click on Execute
Now include this function in procedure as given below:

create procedure "PROC_SALE_INVENTORY"

(
IN "P_ID" integer,
IN "P_NAME" nvarchar(32),
IN "QTY" integer,
OUT Result "TT_SALE_PRODUCT_INVENTORY"
)
language SQLSCRIPT
as begin
declare PROD_ID INTEGER;
DECLARE INV INTEGER;
DECLARE EP DECIMAL(34,2);
DECLARE SALES_AMOUNT DECIMAL(34,2);
SELECT COUNT(*) INTO PROD_ID FROM "PRODUCT_ITEM_PROC" WHERE "P_ID" =:P_ID;
SELECT "STOCK" INTO INV FROM "PRODUCT_MASTER_PROC" WHERE "P_ID" = :P_ID;
SELECT "USP" INTO EP FROM "PRODUCT_ITEM_PROC" WHERE "P_ID" = :P_ID;

IF INV >= :QTY THEN

SALES_AMOUNT:= "SALES_AMOUNT4"(:QTY,::EP);
INSERT INTO "SALES_PROC" ("P_ID","P_NAME", "QTY","SALES_AMOUNT") VALUES (:P_ID,
:P_NAME, :QTY, :SALES_AMOUNT);
UPDATE "PRODUCT_MASTER_PROC" SET "STOCK" = "STOCK" - :QTY WHERE "P_ID" = :P_ID;
END IF;
RESULT = SELECT * FROM "SALES_PROC";
END;

Now execute this procedure using the CALL function:

CALL "PROC_SALE_INVENTORY"(1,'FAN', 10,?);

===================================================================================
==========================================================================
Day 16:
SAP HANA Table Functions in Development Perspective:

SAP HANA Table Functions replaces Scripted Calculation Views as well as it has the
scripted calculation views. It is recommended to use graphical calculation views as
an end product so that the complexity of the information views are
headed inside the table functions. SAP recommends Scripted Based Calculation Views
table functions and then into graphical calculation views. The migration rule
within SAP HANA Modeler converts existing scripted based calculation
views to table functions.

Table Functions has read only user defined functions. It accepts muiltiple input
parameters and returns exactly only one SQL statement. SQL Script is the only
language supported for Table Functions. There are read only statements like
SELECT. Table functions are the read only statements. So you may not use the
statements like INSERT, UPDATE. Table Functions can be executed in the form of
SELECT statement. You can pass the input parameters and you can also
call the function from SQL console. Table function can be used as data foundation
on the graphical calculation views. To build the table function, we will use the
CLIENT and SALES_DOC from the table VBAK. CLIENT technical field name is
MANDT. SALES_DOC field name is VBELN.
Scenario is to build a table function that provides client and sales document from
the table VBAK. For this below process needs to be followed:
(i.) Create package in systems tab
Step 1 :Log into the SAP HANA studio with your credentials.
Step 2 : Enter the password for your server
Step 3: To create Table function, we need to move to the development perspective.
Select SAP HANA Development from open Perspective
Step 4: Go to the content folder. First we need to create a package. Right click on
Content->New->Package
Step 5: Enter the name and description SAPHANA_TF
Step 6: Right click on this package and create sub package by selecting New-
>Package SAPHANA_TF_SD
Now we will create table function
To create a table function we need to switch to the repository.
Step 7: Switch to the repository
Step 8: Right click on SAPHANA_TF package->New-> Table Function->Next
Step 9: Enter file name TABLE_FUNCT
Step 10: Click Finish. Table function has the extension .hdbtablefunction.
FUNCTION "SAPHANA_TF::TABLE_FUNCTION
(
)
RETURNS RETURN_TABLE_TYPE
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN

END;

Above code is generated by the system when clicking on the Table Functions.
FUNCTION is a keyword which marks the SAP HANA Function. Name of the function is a
combination of schema and package.
Any input parameters can be added in the brackets which is used in the table
function. return_table_type is a place holder for the actual one. It is similar to
the column function restricted view. Here we specify the output field.
There are two SQL SECURITY modes: INVOKER and other one is DEFINER. By default we
use INVOKER. This function will invoke a procedure. If you use DEFINER, then the
function executes with the priviledges of the DEFINER.
Its always best to execute it as an INVOKER. Whenever this function is called, it
runs on the basis of user roles and priviledges. Between the Begin and End, we need
to write the logic.

FUNCTION "SAPHANA_TF::TABLE_FUNCTION
(
)
RETURNS TABLE("CLIENT" NVHARCHAR(3), "SALES_DOC" NVARCHAR(10))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN

RETURN
SELECT "MANDT" AS "CLIENT",
"VBELN" AS "SALES_DOC"
FROM "BEST"."VBAK";
END;

Step 11: Activate

Step 12: Execute this code
Step 13: Expand your package and subpackage
Step 14: You will be able to see the table function

Once you create table function, we will now create calculation view that will use
the table function
Step 1: Right click on subpackage SD
Step 2: Select New->Calculation View
Enter the name and description as CV_TF
Step 3: Select Calculation View type as Graphical and Data Category as Dimension
Step 4: Click Finish
Step 5: Click on + symbol in projection
Step 6: Search for the object name TABLE_FUNCTION
Step 7: Select your TABLE_FUNCTION and then press OK
Step 8: Right click on it and select Add all objects
Step 9: Select the fields and then press save and Activate
Step 10: Click on raw data to see the output

How to link Table Functions with Calculation View Input Parameters:

==================================================
Step 1: Go to the table function that you created above
Inside the bracket, its used for input parameters.

FUNCTION "SAPHANA_TF::TABLE_FUNCTION
(
)
RETURNS TABLE("CLIENT" NVHARCHAR(3), "SALES_DOC" NVARCHAR(10))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN

RETURN
SELECT "MANDT" AS "CLIENT",
"VBELN" AS "SALES_DOC"
FROM "BEST"."VBAK";
END;
Here we will use the input parameter which will check the sales document of the
user.
Requirement is to display sales document
FUNCTION "SAPHANA_TF::TABLE_FUNCTION
(
p_sales_doc NVARCHAR(10)
)
RETURNS TABLE("CLIENT" NVHARCHAR(3), "SALES_DOC" NVARCHAR(10))
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS
BEGIN

RETURN
SELECT "MANDT" AS "CLIENT",
"VBELN" AS "SALES_DOC"
FROM "BEST"."VBAK"
WEHRE "VBELN" = :p_sales_doc;
END;
Here where statment tell HANA that we need the output where the input parameter
values matches with the sales document
Step 2: Save and activate
Step 3: Right click on your subpackage SD and select New->Calculation View
Step 4: Provide the name and description as CV_IP
Step 5: Select Calculation View type as Graphical and Data Category as Dimension
Step 6: Click Finish
Step 7: Select + symbol on projection node
Step 8: Search for table function TABLE_FUNCTION
Step 9: Select your TABLE_FUNCTION and then click on OK
Step 10: Right click on the TABLE_FUNCTION and select Add All to Output
We need to create the input parameter for this calculation view with the same
datatype
Step 11: Right click on input parameter folder and select New
Step 12: Enter the name as p_sales_doc and copy the same into the label
Step 13: Select parameter type as Direct
Step 14: Don't select any semantic type and select Datatype as NVARCHAR and length
as 10.
Step 15: Click OK
Input parameter must have the same name and datatype as passed in the table
function
Step 16: Right click on input parameter and select Manage mappings
Step 17: Select Automatic mapping
Step 18: Click OK
Step 19: Click Save and Activate and execute it
Step 20: Click on Raw data to see the data preview
Step 21: Enter the sales document number in the variable/input parameter values
Step 22: Click OK
Step 23: Click on raw data to validate
===================================================================================
==========================================================================
Day 17:

Calculation view can be created in two ways:

(i.) Graphical
(ii.) SQL Script

Graphical calculation views are of three types:

(i.) DIMENSION
(ii.) CUBE
(iii.) CUBE with STAR JOIN

Calculation View using SQL Script:

-------------------------------------------------------
(i.) Right click on your package->New->Calculation View
(ii.) Select Type: SQL Script and Name and Label as : CV_COLUMN_TABLE
(iii.) Click Finish
(iv.) Once you click finish, in the scenario you will se Script View.
(v.) Click File->Import->SAP HANA Content-> Developer Mode
(vi.) Select your server
(vii.) Browse in calculation view folder
(viii.) Click OK
(ix.) Expand Content
(x.) Select HDB package
(xi.) Add some views
(xii.) Click Finish
(xiii.) Now go to HDB
(xiv.) Expand calculation view
(xv.) Double click on calculation view
(xvi.) Click on the script view

You will find the various CE functions:

(a.) CE_OLAP_VIEW: Represents the analytic view with the required

columns(schema.analytic_view_name, [column_name])
(b.) CE_CALC_VIEW: Represents the calculation view.
(c.) CE_PROJECTION: Shows the projection with the required columns
(d.) CE_JOIN: Represents inner join based on condition.

Here CE represents Calculation Engine

Step 1: Go to the any analytic view
Step 2: Right click on any analytic view and select Copy Column View Name
Step 3: Copy it into the notepad
Step 4: Right click on the analytic view and select Data preview
Step 5: Click on raw data
If you want to know the datatype of the column names, copy the table name and then
expand the catalog and then go to the _SYS_BIC schema and expand the same. Right
click on the column views->Filter and then paste
the view name.->Click whole word and then press OK. If you don't match the data
types, you will get the error as the Incompatible data types.

To change the datatypes, click on the Script view and then choose the edit
datatypes for the columns you want to change the data type and then click ok->Click
validate->Click save and activate
Click on Data preview

CE_COLUMN_VIEW("COLUMN_TABLE",[A,B,C])
Column Table - CE_COLUMN_TABLE
Attribute View - CE_JOIN_VIEW
Analytic View - CE_OLAP_VIEW
Calculation View - CE_CALC_VIEW
WHERE HAVING - CE_PROJECTION
GROUP BY - CE_AGGREGATION
INNER JOIN - CE_JOIN
LEFT_OUTER_JOIN - CE_LEFT_OUTER_JOIN
UNION - CE_UNION_ALL

All above functions can be used in the Scripted Calculation Views.

===================================================================================
==================================================================================

In SAP HANA we don't use SQL. We usually use CE Functions.