Issue with Arrays in SQL (Column store error: [34011])

[BTP_Architect]

Hello,

I have an error when using arrays in sql coding in my calculation view

My target is to have the different values of the array.

In this sample coding, I want to have the first value in the array (original coding is much more complex)

I get my data from table ZTAB, only column COL which is NVARCHAR(1)

Coding is as bellow:

BEGIN

DECLARE ma      NVARCHAR(1) ARRAY;
DECLARE ar      NVARCHAR(1) ARRAY;
DECLARE lv_ar   NVARCHAR(1);



lt_tab = select distinct col from ztab;                  
ar := ARRAY_AGG(:lt_tab.col);
lv_ar := :ar[1];                     

var_out = UNNEST(:ma, :ar) AS ("MANDT", "COL");

END ;

The problem come from line 7 (lv_ar := :ar[1];), if I delete line 7 activation will be ok...

For sure it's a conversion error in line 7 but I don't understand why...

Error text:

Internal deployment of object failed;Repository:

Encountered an error in repository runtime extension;Internal

Error:Deploy Calculation View: SQL: transaction rolled back by an internal error:

column store error: [34011] failed to save calculation scenario :

The following errors occured: Inconsistent calculation model (34011)

Thank you in advance for your help.

Moez.

[lbreddemann]

A quick test on Rev 91 didn't reproduce.

Please provide the table def. and some test data (INSERT statements!) so that the test case is self-contained.

[BTP_Architect]

Hello Lars,

My table is created using replication,

But to allow you testing my issue, I created another table in Hana Studio.

Table Creation:

CREATE COLUMN TABLE "xxx_xxx_xxx"."ZCAL_HANA" (
        MANDT NVARCHAR(3),
        WERKS NVARCHAR(4),
        SHIFT NVARCHAR(1),
        PRIMARY KEY (MANDT) );

Insertion:

insert into "xxx_xxx_xxx"."ZCAL_HANA" values('001','1111','1')

Calculation view:

BEGIN
DECLARE ma         NVARCHAR(3) ARRAY;
DECLARE ar      NVARCHAR(1) ARRAY;
DECLARE lv_ar   NVARCHAR(1);
lt_tab = select distinct shift from zcal_hana;                     
ar := ARRAY_AGG(:lt_tab.shift);                    
lv_ar := :ar[1];        ---- Problem is here
var_out = UNNEST(:ma, :ar) AS ("MANDT", "SHIFT");              
END ;

Output parameters

MANDT: NVARCHAR(3)

SHIFT: NVARCHAR(1)

I have the same error noticed in my first post when activating this view...

Thank you in advance for your help,

Moez.

[lbreddemann]

Hi Moez,

I just rebuild the model on Rev. 91 and got no error back...

Really, this should be checked by support then.

-Lars