Playing with schema flexibility on SAP HANA

The other day, a colleague from the Labs asked me an interesting question..."Let's say you have a table with schema flexibility...with a lot of fields that might be empty...how do you know which are fields that have at least one value?"...of course...I didn't knew how to answer that...so of course...I put myself to work...as expected -;)

First...let's create our table with a couple of values and some "missing" values...

Create table Products
DROP TABLE Products; CREATE COLUMN TABLE Products( PRODUCT_CODE VARCHAR(3), PRODUCT_NAME NVARCHAR(20), PRICE DECIMAL(5,2) ) WITH SCHEMA FLEXIBILITY; INSERT INTO Products values ('001','Blag Stuff', 100.99); INSERT INTO Products (PRODUCT_CODE,PRODUCT_NAME,PRICE,COLOR) values ('002','More Blag Stuff',100.99,'Black'); INSERT INTO Products (PRODUCT_CODE,PRODUCT_NAME,PRICE,COLOR,TYPES) values ('003','More Blag Stuff',100.99,null,null);

When we check the contents of the table we will have this...

As we can see...the last field has no values at all...so we will need to retrieve the names of the other fields...for this...we're going to create a table to hold them...then...we're going to create a Procedure to retrieve them...

Create table FIELDS_RECORDS
DROP TABLE Fields_Records; CREATE COLUMN TABLE FIELDS_RECORDS( FIELD_NAME VARCHAR(256) );

For the Procedure...we're going to read all the fields that compose the table...then we're going to count all the values that are not null for each field...for this...we're going to use the help of a temporal table and dynamic SQL as sadly...SQLScript doesn't support scarlar binding yet...

GET_FIELDS_WITH_RECORDS
DROP PROCEDURE GET_FIELDS_WITH_RECORDS; CREATE PROCEDURE GET_FIELDS_WITH_RECORDS(IN TableName VARCHAR(256)) LANGUAGE SQLSCRIPT AS v_column_name VARCHAR(256); v_select VARCHAR(256); v_count INTEGER := 0; CURSOR c_cursor(v_column_name VARCHAR(256)) FOR SELECT COLUMN_NAME FROM SYS.CS_COLUMNS_ A inner join SYS.CS_TABLES_ B on A.table_oid = B.table_oid where schema_name = 'SYSTEM' and table_name = :TableName and internal_column_id > 200 order by internal_column_id; BEGIN FOR cur_row AS c_cursor(v_column_name) DO create local temporary table #temptable(found INTEGER); v_select := 'INSERT INTO #temptable (SELECT COUNT(' || cur_row.column_name || ') FROM ' || :TableName || ' WHERE ' || cur_row.column_name || ' IS NOT NULL)'; exec v_select; SELECT found INTO v_count FROM #temptable; IF v_count > 0 THEN v_select := 'INSERT INTO FIELDS_RECORDS VALUES(''' || cur_row.column_name || ''')'; drop table #temptable; exec v_select; END IF; END FOR; END;

When we call our Procedure, we simply need to pass the name of the table...and then read it's content...

Call Procedure and get info
CALL GET_FIELDS_WITH_RECORDS('PRODUCTS'); SELECT * FROM FIELDS_RECORDS;

Let's see what do we have on FIELDS_RECORDS -;)

Just as expected -:) Nice, huh?

Greetings,

Blag.
Developer Empowerment and Culture.