ARRAY and related functions in HANA SQL
The below post shows ARRAY use and its related functions-
UNNEST( )– Function can be used to convert Array elements to an Internal Table Column.
ARRAY_AGG ( )– Function can be used to convert a column of an Internal Table to an Array.
TRIM_ARRAY( )– Function can be sued to delete array elements from the end.
CARDINALITY( )– Function returns the no of elements/last index of the element in the array.
Below given a class with an AMDP procedure method- and some ARRAY function-
CLASS zcl_amdp_explore DEFINITION PUBLIC FINAL CREATE PUBLIC .
PUBLIC SECTION.
INTERFACES: if_amdp_marker_hdb.
CLASS-METHODS: get_info EXPORTING VALUE(et_scarr) TYPE ty_scarr
RAISING cx_amdp_error.
ENDCLASS.
CLASS zcl_amdp_explore IMPLEMENTATION.
METHOD get_info BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT
OPTIONS READ-ONLY USING scarr.
DECLARE lv_num1 INTEGER ARRAY = ARRAY( 4,8,3,1,0,9,7,6,2,5 );
DECLARE lv_num2,lv_num3,lv_num4,lv_num5,lv_num6,lv_num7 INTEGER ARRAY;
DECLARE lv_index, lv_index_val INTEGER;
— UNNEST( ):Converts array to a internal table
lt_num1 = unnest(:lv_num1) as (val);
— ARRAY_AGG ( ): Converts an internal table column to an array
lv_num2 = ARRAY_AGG (:lt_num1.val);
lv_num3 = ARRAY_AGG (:lt_num1.val ORDER BY val asc);
lv_num4 = ARRAY_AGG (:lt_num1.val ORDER BY val desc);
— TRIM_ARRAY( )- Removes elemets from the end of the array
lv_num5 = TRIM_ARRAY (:lv_num2, 5);
— combines( no add but append) two array elements into one array element
lv_num6 = CONCAT (:lv_num3, :lv_num4);
lv_num7 = :lv_num3 || :lv_num4 ;
— CARDINALITY( )-Returns the highest index of the element in array
lv_index = CARDINALITY (:lv_num2);
— checks can be performed
IF CARDINALITY (:lv_num2) > 0 THEN
lv_index_val = :LV_NUM2[ 1];
END IF ;
et_scarr = SELECT * FROM scarr
WHERE mandt = Session_context( ‘CLIENT’ );
ENDMETHOD.
ENDCLASS.
A test program
LV_NUM1 is an INTEGER array declared and initialized with default elements.
The UNNEST function converts the array elements to an internal table with table column name as VAL.
The ARRAY_AGG( ) = functions converts an internal table column to an array with ASC and DESC addition to make sort of the values.
TRIM_ARRAY( )- Function used to delete elements from the end of the array with a given length.
A simple [ CONCAT or || ] function can be applied on two arrays to produce a final aary with combined(append) elements.
CARDINALITY( )- function returns the last index of the element of the array and this also can be used to check the array is initial or not. Once you get the cardinality then this can be used to access the element or insert an element to the array.
SAPCODES 