I am new to SAP HANA as developer. I am in a situation where I have no idea what data is going to be input by users and I want to go through the cells of the table provided by users. In order to achieve that, I would like to select any column by referring to their positions in table.
The code is
/*Creation of table*/
DROP TABLE TEST_TABLE2;
DROP PROCEDURE TEST_TABLE2;
CREATE COLUMN TABLE TEST_TABLE2 (X INT, Y INT, Z INT);
INSERT INTO TEST_TABLE2 VALUES (1,2,3);
INSERT INTO TEST_TABLE2 VALUES (4,5,6);
INSERT INTO TEST_TABLE2 VALUES (7,8,9);
INSERT INTO TEST_TABLE2 VALUES (10,11,12);
DROP TABLE TEST_TABLE2_PROPERTY;
/*Creation of table containing column names and position*/
CREATE COLUMN TABLE TEST_TABLE2_PROPERTY AS (SELECT COLUMN_NAME, POSITION FROM TABLE_COLUMNS WHERE SCHEMA_NAME = CURRENT_SCHEMA AND TABLE_NAME = 'TEST_TABLE2' ORDER BY POSITION);
/*Attempt to extract column number 2 in TEST_TABLE2, which should be column Y*/
SELECT (SELECT COLUMN_NAME FROM TEST_TABLE2_PROPERTY WHERE POSITION = 2) FROM TEST_TABLE2;
The expected output is a table with only column Y of TEST_TABLE2, but the weird output comes up
Could anyone help explain why such weird output what should I do to get desired output?
Hello
This is possible using dynamic SQL to construct a statement. Check the documentation and various SCN posts on this topic.
Michael
Add a comment