SQL/ARRAY Data Type at master · flyzhenghao/SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- this syntax is used for testing the ARRAY data type in SAP HANA

CREATE COLUMN TABLE TAB1 ( COL1 INT PRIMARY KEY, COL2 INT ARRAY );
INSERT INTO TAB1 VALUES ( 4, ARRAY ( 1, 2, 3, 4.00001 ) );

DELETE FROM TAB1 WHERE 3 MEMBER OF COL2;
INSERT INTO TAB1 VALUES ( 1, ARRAY ( 21, 22, 23, 34 ) );
UPSERT TAB1 VALUES ( 1, ARRAY ( 21, 22, 23, 24 ) ) WHERE COL1 = 1;

SELECT COL2[4] FROM TAB1;
SELECT MEMBER_AT(COL2,4) FROM TAB1;

SELECT CARDINALITY(COL2) FROM TAB1;

SELECT * FROM UNNEST(TAB1.COL2) AS TAB1_UNNESTED(MEM);

INSERT INTO TAB1 VALUES ( 2, ARRAY ( 10, 20, 30 ) );
SELECT * FROM UNNEST(TAB1.COL2) WITH ORDINALITY AS TAB1_UNNESTED(MEM);

INSERT INTO TAB1 VALUES (
	3, ARRAY ( 21, 22, 23, 24 ) || ARRAY ( 10, 20, 30, NULL, 40, NULL)
);