SAP HANA Tutorial, Material and Certification Guide

In the previous article SAP HANA Procedure - Old Wine in New Bottle we learnt what is Procedure and how to create a procedure using SQL editor.

In this article we will learn a better and simpler way of creating procedure using Modeler Perspective.
Prerequisites:

Before creating a procedure as part of this exercise, we need to create the tables that will be used. Let us do that.
This is similar as we did in our previous article SAP HANA Procedure - Old Wine in New Bottle

Example Scenario:

Suppose there are 3 tables PRODUCTS, REGION and SALES.

We need to find out sales value for different region. We also need to calculate NET_AMOUNT based on the DISCOUNT. DISCOUNT value will be passed as input parameter.

We will create a procedure to achieve this.

Create Tables:

1. Open HANA Studio and expand the SAP HANA system.

2. Go to your schema. Right-click on your schema and select SQL editor.

Note: In this example schema name is "SAP_HANA_TUTORIAL". In case you want to create a new schema use below query.

create schema <schema_name>;

3. Copy and paste the below script in SQL editor and execute.

----REPLACE <YOUR SCHEMA> WITH YOUR SCHEMA NAME

---- Create Product table

create column table "<YOUR SCHEMA>"."PRODUCT"(

"PRODUCT_ID" INTEGER,

"PRODUCT_NAME" VARCHAR (100),

primary key ("PRODUCT_ID")

);

insert into "<YOUR SCHEMA>"."PRODUCT" values(1,'Shirts');

insert into "<YOUR SCHEMA>"."PRODUCT" values(2,'Jackets');

insert into "<YOUR SCHEMA>"."PRODUCT" values(3,'Trousers');

insert into "<YOUR SCHEMA>"."PRODUCT" values(4,'Coats');

insert into "<YOUR SCHEMA>"."PRODUCT" values(5,'Purse');

-- Create Region table

create column table "<YOUR SCHEMA>"."REGION"(

"REGION_ID" INTEGER,

"REGION_NAME" VARCHAR (100),

"SUB_REGION_NAME" VARCHAR (100),

PRIMARY KEY ("REGION_ID")

);

insert into "<YOUR SCHEMA>"."REGION" values(100,'Americas','North-America');

insert into "<YOUR SCHEMA>"."REGION" values(200,'Americas','South-America');

insert into "<YOUR SCHEMA>"."REGION" values(300,'Asia','India');

insert into "<YOUR SCHEMA>"."REGION" values(400,'Asia','Japan');

insert into "<YOUR SCHEMA>"."REGION" values(500,'Europe','Germany');

-- Create Sales table

create column table "<YOUR SCHEMA>"."SALES"(

"REGION_ID" INTEGER ,

"PRODUCT_ID" INTEGER ,

"SALES_AMOUNT" DOUBLE, PRIMARY KEY ("REGION_ID", "PRODUCT_ID") );

insert into "<YOUR SCHEMA>"."SALES" values(100,1,100);

insert into "<YOUR SCHEMA>"."SALES" values(100,2,90);

insert into "<YOUR SCHEMA>"."SALES" values(100,5,85);

insert into "<YOUR SCHEMA>"."SALES" values(200,2,80);

insert into "<YOUR SCHEMA>"."SALES" values(200,1,75);

insert into "<YOUR SCHEMA>"."SALES" values(300,3,85);

insert into "<YOUR SCHEMA>"."SALES" values(400,4,75);

insert into "<YOUR SCHEMA>"."SALES" values(500,1,65);

insert into "<YOUR SCHEMA>"."SALES" values(500,2,65);

4. After executing the scripts you should have 3 tables created. If there are no tables, try right-clicking on your schema and refreshing.

Grant schema SELECT rights to _SYS_REPO user:

Open the SQL editor of your schema and execute the following command line:

GRANT SELECT ON SCHEMA <YOUR SCHEMA> TO _SYS_REPO WITH GRANT OPTION;

If you miss this step, an error will occur when you activate your views later.

Click here to know more about this.

Steps to Create The Procedure:

1. Right click on the package and go to New -> Procedure.

2. Specify name and description and click on Finish.

3. Paste the below code between BEGIN and END.

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;

4. On the right side "Output pane" there is "Output Parameters". Right click on that and click on New. Create the output parameter as shown below. Note that this has to be exactly same as last SQL statement of the procedure.