Using SQLSCRIPT ARRAY as “Internal Table” to deal with complex logic
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
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.
This is a really nice solution to the problem of how to consume WRITE procedures 🙂 (by avoiding them altogether).
Thanks for the interesting blog, Steve.
I am also stuck with the compile time errors for decimal ARRAY_AGGs. Every "conversion" would eventually read from that Array, right?
FYI: there is no reason to "be stuck" because the decimal issue. A work around is to move the data into a new table variable and do the conversion:
newTab = select field1, TO_DOUBLE(measure1) from oldTab
That works perfectly well, but maybe there is a possibilty to read directly from oldTab into the arraysas I asked Steve below.
Hi äh.. Mr Me 😉
right, thanks for the hint. I had to add an "AS measure1", then it activated.
"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."
For the ARRAY_AGG to work the array must be of type decimal if the table's field is of type decimal.
How do you do the conversion of reading a DECIMAL field into a DOUBLE array?
Using to_double sql function in SELECT Statement
Great post. I am getting an error unsupported data type used when i am trying to work around with arrays.
something like select * from table1 where column1= :arrayvar[:index]
i have declared arrayvar as an integer array. and index as integer. what may be the issue?
Hi safiyudeen,save :arrayvar[:index] into a temprory variable to solve this problem.
Thnks alot... 🙂
Nice blog, thanks for the tips 😉
Nice blog, thanks
Nice Blog, I use the array way instead of insert the value into temp table each time in Loop. Comparing the performance, code using INSERT and ARRAY, they did not varry too much in my customer scenario. 😉 But using ARRAY can directly put the code into read-only column view/hana model script view, instead of putting all the logic in HANA procedure. 😛
Thanks for the sharing!
You mention "Up to now, SQL Script do not support directly pass array value to a procedure or function".
Will your example work in SPS8 rev82?
No need to reply, i got it to work! Thanks!
PS: Interestingly I'm not having any issue using decimal(17,2) so I'm not sure why. Seems to be working correctly for me.
nice blog, its really helpful.
can i also use array values in calculation .. i am using n below manner but getting error Identifier must be declared for a_val1
A_val1 integer array;
A_val2 integer array;
A_val2[:ind] = a_val1[:ind] +a_val1[:ind + 1];
but i am getting error "Identifier must be declared for a_val1"
Hi you need to put the "a" uppercase like "A_val1[:ind] +A_val1[:ind + 1];"