SAP HANA SQLScript

 

SQLScript is a collection of extensions to Structured Query Language (SQL). The extensions are:

  • Data extension – which allows the definition of table types without corresponding tables.
  • Functional extension – which allows definitions of (side-effect free) functions which can be used to express and encapsulate complex data flows.
  • Procedural extension – which provides imperative constructs executed in the context of the database process.

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:

  • The calculation and transformations described in procedures can be parameterized and reused in other procedures.
  • The user is able to use and express knowledge about relationships in the data; related computations can share common sub-expressions, and related results can be returned using multiple output parameters.
  • It is easy to define common sub-expressions. The query optimizer decides if a materialization strategy (which avoids recomputation of expressions) or other optimizing rewrites are best to apply. In any case, it eases the task to detect common sub-expressions and improves the readability of the SQLScript code.
  • Scalar variables or imperative language features are also available and can be used if they are required.

How to create Procedures

There are two ways to create procedures:

  • Using SQL Editor
  • Using Modeler Perspective

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…

    • Product
    • Region
    • Sales

                    Tables.PNG

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…

    1. Create Tables
      1. Open SAP HANA Studio and expand the SYSTEM
      2. Go to your schema and right click on the schema and select SQL Console (Note-SAP_HANA_TUTORIAL is the Schema used for demo here)SQL Console.PNG
      3. Copy and Paste below code segment in SQL editor and

                        

—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

               SQL Console.PNG

          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

    1. Right click on package and select New –> PROCEDUREProcedure.PNG
    2. Put Name & Lebel as per below and click FINISH HANA-Procedure-4.jpg
    3. Paste below Code segment between BEGIN and END.

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.

HANA-Procedure-5.jpg

e. On Input pane, click on the ‘Input Parameters’ and select New Scalar Parameter and create as DISCOUNT.

HANA-Procedure-6.jpg

f. Activate by clicking on Active button on top right corner. The procedure will be activated and see the Log with Completed Successfully message.

HANA-Procedure-7.jpg

g. Runtime object of the procedure can be seen in _SYS_BIC schema.

HANA-Procedure-8.jpg

h. Call the procedure using CALL statement in SQL console with below command

     CALL “_SYS_BIC“.”SAP-HANA-TUTORIAL/Procedure_SalesReport”(100, NULL);

HANA-Procedure-9.jpg

We have now completed the tutorial and I hope it helped you. BIG Thank you…Cheers !

Ajay Kumar

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Rich Heilman

    Ajay,

    We are not recommending the use of the modeler perspective to created procedures since SP05.  Since SP05, we have recommended to create procedures using the SAP HANA Development perspective using the SQLScript Editor and debugger. We also recommend to use the editor/debugger for procedures in the Web Based Development Workbench(in SP09) as well.  This tutorial is basically showing the “old” way we use to create procedures in SP04 and before.

    Cheers,

    Rich Heilman

    SAP HANA Product Management

    (0) 

Leave a Reply