Using SQLSCRIPT ARRAY as "Internal Table" to deal with complex logic

[former_member210479]

SQL Script using a declarative style to simplify our data operation. Although HANA SQL Statement is a powerful language, in some cases, especially complex calculation is needed, we need cache table in memory and operate it, just like internal table did in ABAP.

Table variable in SQL Script is a symbol to describe some SQL statement or other logic. It is not a “real” variable at runtime. SQL script’s execution is not line by line, our script was analyzed by HANA and was treated like one or several combined SQL statement. The consequence is we can do nothing with intermediate data you declared in SQL Script as table variable.

Create local temporary table may solve the problem, we could create some temporary table and using update or insert statement to manipulate data in it. But we need to create procedures with write privilege. Thus could not be invoked by read only procedures and calculation views.

Fortunately, SQL Script has an array concept, it could help us to store table data temporary in memory and manipulate it.

In this article, we use a sap standard table SCARR as example to show how to use ARRAY to handle data in memory.

1.Declare Array for storing data

SQL Scrpit do not has a  "Structure" or "Internal Table" concept, so if we want to save data in a table, we should use some arrays, each represent a field.

DECLARE SCARR_MANDT NVARCHAR(3) ARRAY; --Declare a array of  nvarchar(3)

DECLARE SCARR_CARRID NVARCHAR(2) ARRAY;

DECLARE SCARR_CARRNAME NVARCHAR(10) ARRAY;

DECLARE SCARR_INDEX INTEGER;--Declare an integer for record the pointer of arrays

• You could use any primitive data types of sql scrpit.
• Mention that,for some reason, may be some bugs, if you use DECIMAL type in array and try to read its value will lead to a compile error. So if you want to deal with decimals, use double instead and convert it back to decimal after calculation.
• Following a [TABLENAME]_[FIELDNAME] rule to ensure code is easy to understand.

2.Fill arrays with values from a table variable

After binding a select statement to a table variable, using ARRAY_AGG command to fill column data into array

LT_SPFLI = SELECT MANDT,CARRID,CARRNAME FROM SPFLI;

SCARR_MANDT = ARRAY_AGG(:LT_SPFLI.MANDT ORDER BY MANDT,CARRID);

SCARR_CARRID = ARRAY_AGG(:LT_SPFLI.CARRID ORDER BY MANDT,CARRID);

SCARR_CARNAME = ARRAY_AGG(:LT_SPFLI_CARRNAME ORDER BY MANDT,CARRID);

• Sort data by primary key to ensure each array have the same order, then we could use one index to access fields of same record
• Mention to the use of ":"
• Data types of array element and table column must be same

3.Loop over,get data from or set value to the array

FOR SCARR_INDEX IN 1 .. CARDINALITY(:SCARR_MANDT) DO --Using cardinality statement to get number of elements of an array. Array index starts from 1.

IF :SCARR_CARRID[:SCARR_INDEX] = 'AA' THEN --Get value using :ARRNAME[:INDEX]

SCARR_CARRNAME[:SCARR_INDEX] := 'America Airline'; --Set value using ARRNAME[:INDEX] := VALUE

END IF;

END FOR;

SCARR_INDEX := CARDINALITY(:SCARR_MANDT) + 1; --Add 1 to the index to add new rows

SCARR_MANDT[:SCARR_INDEX] := '200'; --Set value with new index directly, be careful not to override existing values

SCARR_CARRID[:SCARR_INDEX] := 'CA';

SCARR_CARRNAME[:SCARR_INDEX] := 'China Airline';

• Up to now, SQL Script do not support directly pass array value to a procedure or function, use a temp sclar variable to handle the data temproryly.
• Mention to the use of ":",":=" and '='

4.Combine arrays to a table variable

Using UNNEST command to combine arrays to a table variable

var_out = UNNEST(:SCARR_MANDT,:SCARR_CARRID,:SCARR_CARRNAME) AS ("MANDT","CARRID","CONNID");

--Arrays are transfered to columns of a table

5.Summary

Using array, we can deal with complex logic which could not deal with SQL statement without create temprory tables, this feature make SQL Script has the ability to handle nearly all data logic.Except this,we could force hana to execute some logic at a certain time and sequence to improve proformence in some case.Although doing those is not as easy as ABAP, we have a way to deal with the most complex logic and combine those logic to the easy unstanding declaretive SQL Script.