SapHanaTutorial.Com HOME     Learning-Materials Interview-Q&A Certifications Quiz Online-Courses Forum Jobs Trendz FAQs  
     Explore The World of Hana With Us     
About Us
Contact Us
 Apps
X
HANA App
>>>
Hadoop App
>>>
Tutorial App on SAP HANA
This app is an All-In-One package to provide everything to HANA Lovers.

It contains
1. Courses on SAP HANA - Basics, Modeling and Administration
2. Multiple Quizzes on Overview, Modelling, Architeture, and Administration
3. Most popular articles on SAP HANA
4. Series of Interview questions to brushup your HANA skills
Tutorial App on Hadoop
This app is an All-In-One package to provide everything to Hadoop Lovers.

It contains
1. Courses on Hadoop - Basics and Advanced
2. Multiple Quizzes on Basics, MapReduce and HDFS
3. Most popular articles on Hadoop
4. Series of Interview questions to brushup your skills
Apps
HANA App
Hadoop App
';
Search
Stay Connected
Search Topics
Topic Index
+
-
SQLScript
+
-
Tables in SAP HANA
+
-
Procedure
+
-
Examples of Procedure
Use of Array in Procedure


In this article we will show an example on - How to create and use Array inside procedures.

Introduction:

In SQLScript array can be declared using ARRAY function.
Example 1:
Define an integer array that contains the numbers 1,2 and 3.
            array_id INTEGER ARRAY[] := ARRAY(1, 2, 3);

Example 2:
Define an empty array of type INTEGER.
            array_int INTEGER ARRAY;

UNNEST Function:

The UNNEST function converts an array into a table. UNNEST returns a table including a row for each element of the array specified.
If there are multiple arrays given, the number of rows will be equal to the largest cardinality among the cardinalities of the arrays. In the returned table, the cells that are not corresponding to the elements of the arrays are filled with NULL values.
Note: The UNNEST function cannot be referenced directly in FROM clause of a SELECT statement.

In this example we are going to create 4 arrays and create a table using the arrays. The output will be sent in output using a table type.

Create table type:


CREATE TYPE "<SCHEMA_NAME>".TT_PRODUCT_SALES  AS TABLE(
           "PRODUCTID" VARCHAR(100),
           "CATEGORY" VARCHAR(100),
           "PRICE" DECIMAL(15,2),
           "SALEPRICE" DECIMAL(15,2)
);

To know more about table type, read SAP HANA Table Type.

Create procedure:

Copy and paste the below script to create the procedure.

--REPLACE <SCHEMA_NAME> WITH YOUR SCHEMA NAME
CREATE PROCEDURE <SCHEMA_NAME>."PRODUCT_ARRAY"(
            OUT output_table <SCHEMA_NAME>."TT_PRODUCT_SALES" )
    LANGUAGE SQLSCRIPT
    SQL SECURITY INVOKER
    AS
/*********BEGIN PROCEDURE SCRIPT ************/
BEGIN
      DECLARE productid VARCHAR(20) ARRAY;
      DECLARE category VARCHAR(20) ARRAY;
      DECLARE price DECIMAL(15,2) ARRAY;
      DECLARE saleprice DECIMAL(15,2) ARRAY;
      
      productid[1] := 'ProductA';
      productid[2] := 'ProductB';
      productid[3] := 'ProductC';

      category[1] := 'CategoryA';
      category[2] := 'CategoryB';
      category[3] := 'CategoryC';
      
      price[1] := 19.99;
      price[2] := 29.99;
      price[3] := 39.99;
                   
      saleprice[1] := 15.99;
      saleprice[2] := 25.99;
      saleprice[3] := 35.99;
     
      output_table = UNNEST(:productId, :category, :price, :saleprice)
        AS ("PRODUCTID", "CATEGORY", "PRICE", "SALEPRICE");
END;


Call procedure:

Call the procedure using below statement.
CALL <SCHEMA_NAME>."PRODUCT_ARRAY"(?)

SAP HANA Procedure Example

Continue reading:
Check the next example on Dynamic Filter in HANA




Support us by sharing this article.



Explore More
Close X
Close X

Leave a Reply

Your email address will not be published. Required fields are marked *

Current day month ye@r *

 © 2017 : saphanatutorial.com, All rights reserved.  Privacy Policy