SAP HANA SQLScript
SQLScript is a collection of extensions to Structured Query Language (SQL). The extensions are:
Procedures
Procedures allows you to describe a sequence of data transformations on data passed as input and database tables. Data transformations can be implemented as queries that follow the SAP HANA database SQL syntax by calling other procedures. Read-only procedures can only call other read-only procedures.
The use of procedures has some advantages compared to using SQL:
How to create Procedures
There are two ways to create procedures:
SAP HANA Procedures using Modeler Perspective
It is same old wine in new bottle
Prerequisite
Before creating HANA procedures make sure that you have all the tables created that will be used here.
Lets take a scenario
We have 3-tables...
Requirement is to find out sales values for different region. Also need to calculate SALES_AMOUNT based on DISCOUNTS, which will be taken as INPUT parameter. So, lets create a HANA procedure to achieve this requirement by followings steps...
---Create Product Table
create colunm table "SAP_HANA_TUTORIAL"."PRODUCT"(
"PRODUCT_ID" INTEGER,
"PRODUCT_NAME" VARCHAR(100),
primary key("PRODUCT_ID"));
INSERT INTO "SAP_HANA_TUTORIAL"."PRODUCT" VALUE(1, 'Shirts');
INSERT INTO "SAP_HANA_TUTORIAL"."PRODUCT" VALUE(2, 'Jackets');
INSERT INTO "SAP_HANA_TUTORIAL"."PRODUCT" VALUE(3, 'Trousers');
INSERT INTO "SAP_HANA_TUTORIAL"."PRODUCT" VALUE(4, 'Coats');
INSERT INTO "SAP_HANA_TUTORIAL"."PRODUCT" VALUE(5, 'Purse');
---Create Region Table
create colunm table "SAP_HANA_TUTORIAL"."REGION"(
"REGION_ID" INTEGER,
"REGION_NAME" VARCHAR(100),
"SUB_REGION_NAME" VARCHAR(100),
primary key("REGION_ID"));
INSERT INTO "SAP_HANA_TUTORIAL"."REGION" VALUE(100, 'Americas', 'North-America');
INSERT INTO "SAP_HANA_TUTORIAL"."REGION" VALUE(200, 'Americas', 'South-America');
INSERT INTO "SAP_HANA_TUTORIAL"."REGION" VALUE(300, 'Asia', 'India');
INSERT INTO "SAP_HANA_TUTORIAL"."REGION" VALUE(400, 'Asia', 'Japan');
INSERT INTO "SAP_HANA_TUTORIAL"."REGION" VALUE(500, 'Europe', 'Germany');
---Create Sales Table
create colunm table "SAP_HANA_TUTORIAL"."SALES"(
"REGION_ID" INTEGER,
"PRODUCT_ID" INTEGER,
"SALES_AMOUNT" DOUBLE,
primary key("REGION_ID", "PRODUCT_ID" ));
INSERT INTO "SAP_HANA_TUTORIAL"."SALES" VALUE(100,1,100);
INSERT INTO "SAP_HANA_TUTORIAL"."SALES" VALUE(100,2,90);
INSERT INTO "SAP_HANA_TUTORIAL"."SALES" VALUE(100,5,85);
INSERT INTO "SAP_HANA_TUTORIAL"."SALES" VALUE(200,2,80');
INSERT INTO "SAP_HANA_TUTORIAL"."SALES" VALUE(200,1,75);
INSERT INTO "SAP_HANA_TUTORIAL"."SALES" VALUE(300,3,85);
INSERT INTO "SAP_HANA_TUTORIAL"."SALES" VALUE(400,4,75);
INSERT INTO "SAP_HANA_TUTORIAL"."SALES" VALUE(500,1,65);
INSERT INTO "SAP_HANA_TUTORIAL"."SALES" VALUE(500,2,65);
d. Once you execute the above code, you will have 3-tables created with data. To view, right click on schema and refresh
e. Grant schema SELECT rights to _SYS_REPO user, By using below direct SQL code in SQL console
GRANT SELECT ON SCHEMA SAP_HANA_TUTORIAL TO "_SYS_REPO".
2. Create Procedure
BEGIN
VAR1 = SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T2.PRODUCT_ID, T2.SALES_AMOUNT,
FROM SCHEMA_NAME.REGION AS T1
INNER JOIN
SCHEMA_NAME.SALES AS T2
ON T1.REGION_ID = T2.REGION_ID;
VAR2 = SELECT T1.REGION_NAME, T1.SUB_REGION_NAME, T1.PRODUCT_ID, T1.SALES_AMOUNT, T2.PRODUCT_NAME
FROM :VAR1 AS T1
INNER JOIN
SCHEMA_NAME.PRODUCT AS T2
ON T1.PRODUCT_ID = T2.PRODUCT_ID;
OUTPUT_TABLE = SELECT SUM(SALES_AMOUNT) AS SALES_AMOUNT, SUM(SALES_AMOUNT - (SALES_AMOUNT*:DISCOUNT/100)) AS NET_AMOUNT,
PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME
FROM :VAR2
GROUP BY PRODUCT_NAME, REGION_NAME, SUB_REGION_NAME;
END;
d. Locate the Output Pane on right side of the page and click on 'Output Parameters' and select new. Create the output parameter as shown below.
e. On Input pane, click on the 'Input Parameters' and select New Scalar Parameter and create as DISCOUNT.
f. Activate by clicking on Active button on top right corner. The procedure will be activated and see the Log with Completed Successfully message.
g. Runtime object of the procedure can be seen in _SYS_BIC schema.
h. Call the procedure using CALL statement in SQL console with below command
CALL "_SYS_BIC"."SAP-HANA-TUTORIAL/Procedure_SalesReport"(100, NULL);
We have now completed the tutorial and I hope it helped you. BIG Thank you...Cheers !
Ajay Kumar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
37 | |
10 | |
5 | |
4 | |
4 | |
3 | |
3 | |
3 | |
2 | |
2 |