Concatenation two arrays in HANA stored procedure

Question

I try to perform Array concatenation. The code used is as shown below:

--CONCATENATE TWO ARRAYS

CREATEPROCEDURE"keerthi"."ARRAY_COMPLEX_CONCAT3"
(OUT OUTTAB TABLE (SEQ INT,ID INT)) 
LANGUAGE SQLSCRIPT AS BEGIN
DECLARE id1,id2,id3,id4 , id5 INTEGER ARRAY;
id1[1]  := 1;
id1[2]:=2; 
id2[1]  := 1;  
id2[2]:=4;  
id3 := CONCAT(:id1, :id2);  
id4 := :id1 || :id2;  
rst = UNNEST(:id3) WITH ORDINALITY AS ("ID","SEQ");  
id5 := :id4 || ARRAY_AGG(:rst."ID"ORDERBY"SEQ");
outtab = SELECT SEQ,ID FROM :rst ORDERBY "SEQ";
END;

The procedure is executed successfully but the result is not what I expect:

| Seq |  Id |
--------------
|  1  |  1  |
|  2  |  2  |
|  3  |  1  |
|  4  |  4  |

What I expect is:

| Seq |  Id |
--------------
|  1  |  1,2 |
|  2  |  1,4 |

Answer 1

The ARRAY_AGG function does not "aggregate and group array cell values" as your expected outcome implies.

Instead, it takes a column from a table variable and makes an array out of it. Each single column value gets its own cell. The result is an array with the same number of entries than rows in the table variable.

If you don't require to have Id in the outtab to be an ARRAY type, then you could look into using the STRING_AGG aggregation function.