Create procedure using the Modeler wizard

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. 

5. 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. 

6. Click on Activate button on the top right corner. The procedure will be activated and you can see a successful message in Job Log. 

7. The runtime object of this procedure is created in _SYS_BIC schema. You may check that. 

8. Call the procedure using CALL statement. 

CALL "_SYS_BIC"."sap-hana-tutorial/Procedure_SalesReport" (100, NULL);

Note: The 3rd approach - "Create procedure using SAP HANA –XS project" will be discussed later in HANA XS course.

Click here to go to Next Chapter. 5.5 Quiz