Array processing in HANA

[AndyAnand]

Hi all,

I am using release 64 HANA SPS 6. 

I have a question on how to compare array elements. Here is what I have so far:

1) Used array_agg to get two columns of a table

2) I iterate over those, and compare values in Column A to Column B

                     IF  :a[:i]  =  :b[:i]  then ....

                     END IF.

However, I get an error saying array A comparison with array B is not supported.  (note that both are NVARCHAR(12) arrays)

3) I tried to assign the values to two scalars, so as to compare the scalars thereafter, but converting ONE value in an array to a scalar is not supported either.

Any pointers on how to go about comparing values in arrays ?  

Does anyone have a working example of array processing ? (beyond just "array_agg" and "unnest" ? )

Regards,

Andy Anand

[kevin_small]

Hi Andy,

Did you find a way to read an array element into a scalar? 

According to the SQL script guide linked to above, section 7.7.4 "RETURN AN ELEMENT OF AN ARRAY" says that this sort of code should work:

CREATE PROCEDURE test (OUT output integer) READS SQL DATA AS

BEGIN

DECLARE id INTEGER ARRAY := ARRAY(1, 2, 3);

DECLARE n INTEGER := 1;

output := :id[:n];

END;

I am using SPS06 and Studio Rev 62 and the above code does not compile.  It complains about this line, saying that the "[" is misplaced:

output := :id[:n];

Thanks,

Kevin.

[kevin_small]

I think I can answer this myself - I've just noticed that even though the editor shows a red X error at that line, the procedure DOES actually compile ok and can be executed.

[AndyAnand]

Thanks for responding, Prabhith! I have read that document .. here is an example from Pg 79 of that manual.

CREATE PROCEDURE ARRAY_UNNEST()

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

    DECLARE amount INTEGER ARRAY := ARRAY(10, 20);

    DECLARE fruit VARCHAR(10) ARRAY := ARRAY('Orange', 'Kiwi', 'Grape');

    rst = UNNEST(:fruit, :amount) WITH ORDINALITY AS ("FRUIT", "AMOUNT", "SEQ");

    SELECT SEQ, FRUIT, AMOUNT FROM :rst;

END;

CALL ARRAY_UNNEST();

I copied-and-pasted the above example into my HANA system, and it will not even compile!

Error is   String is incompatible with Varchar3

Hence I was wondering if someone out there has actually played with manipulating arrays, and can post their experiences ? If there is, then I need to head over to my HANA installation guys

Thanks again .. and Kind Regards,

Andy Anand

[sorin_radulescu]

I tried that code as well and it is working fine.

What version of HANA are you using?

Regards

Sorin Radulescu

[AndyAnand]

hi Sorin,

I am on release 61 (sorry about mentioning release 64 above.. My server admins had mentioned 64 couple of weeks back .. but I guess the upgrade was never executed).

Which release are you on ?

Regards,

Andy Anand         

[sorin_radulescu]

My test was done using 64 and 67. It was working in both cases.

Regards

Sorin

[Prabhith]

Hi ,

Please see if following document gives  you some help.

See page 73 from the reference guide.

http://help.sap.com/hana/SAP_HANA_SQL_Script_Reference_en.pdf

BR

Prabhith