This tutorial is about how to write SQL script in SAP HANA stored procedure in order to create a complex calculation on the fly to generate a report from pulling the data from the table
Table definition
Below we can see a table with four columns as follows:
DEPTID(INT)
CITY (NVARCHAR)
QUARTER (NVARCHAR)
SALARY (DECIMAL)
The commission percentage is based on department ID's therefore,we have 3 department ids
DEPTID1 = 20% of salary
DEPTID2 = 30% of salary
DEPTID3 = 40% of salary
We need a complex calculation which we can build by using a case expression by using a stored procedure.
Therefore we move over to HANA system where we see a table DEPTTOTPAYROLL with following:
3 Department Ids
DEPTID1, DEPTID2, DEPTID3
3 CITY
SEATLE, TACOMA, REDMOND
AND QUARTER
Q1, Q2, Q3
Creating SQL Procedure
For creating SQL procedure we must first go to SQL Interface
In SQL Interface please run this code to create SQL procedure DEPT_PAYROLL_COM
Create Procedure SCHEMANAME.DEPT_PAYROLL_COMM (IN VarDepID INTEGER, IN VarQuartr NVARCHAR (2)) LANGUAGE SQLSCRIPT AS
BEGIN
SELECT "DEPTID", "CITY", "QUARTER", "SALARY"
CASE "DEPTID"
WHEN 1 THEN "SALARY" = 0.20
WHEN 2 THEN "SALARY" = 0.30
WHEN 3 THEN "SALARY" = 0.40
END AS COMISSION
FROM "SCHEMANAME" . "DEPTTOTPAYROll"
WHERE "DEPTID" = :VarDepID AND "QUARTER" = :VarQuartr
END;
Note: Variable :VarDepID and :VarQuartr which is used to pull information.
After creating the SQL Procedure we can call the procedure by executing this code
call "SCHEMANAME" . "DEPT_PAYROLL_COMM" (2. 'Q3')
