Skip to Content
-1
Nov 15, 2018 at 11:45 PM

SAP HANA Table Name Matches a Column Value in another Table

503 Views Last edit Nov 16, 2018 at 05:37 AM 2 rev

Hello,

I have a custom table in Hana with multiple key figures and multiple attributes. The columns name in table 1 are column values in table 2. I need to select * from table 1 where the column name OF TABLE 1 ( Key in table 2) is in values of table 2.

Say this is table 1 and it has the columns PRODUCT,COLOR,TYPE and QUANTITY.

TABLE 1PRODUCTCOLORTYPEQTYP1GREENTOY10P1REDKITCHEN10P2GREENDINING20P3REDTOY30P4VIOLETTOY40P5BLUETOY50

There is a TABLE 2 which has columns USER_ID, KEY AND VALUE.TABLE 2USERIDKEYVALUEU1COLORGREENU1COLORBLUEU1TYPETOYU2COLORREDU2COLORBLUE

I need to filter from Table1 based on the user id and key and value. For User1 U1 I need to filter from TABLE 1 where color in ( 'green','blue') and TYPE = 'toy'. There are 20 such attributes that are in table 1 like Color and Type. For a single user, not all 20 attributes will not be available in table 2. Like in the above table for user U2, table 1 needs to be filtered on only COLOR and not type. This is an example that I have given and TABLE 1 has 20 million rows and so I need a performance effective solution.

What I have done is I have selected for a User, I have done SELECT COUNT(*) INTO COLOR_COUNT FROM TABLE2 WHERE KEY = 'COLOR'

I have again done SELECT COUNT(*) INTO TYPE_COUNT from TABLE2 WHERE KEY = 'TYPE'.

Then I do SELECT * FROM TABLE1 WHERE ( COLOR_COUNT = 0 OR COLOR IN (SELECT DISTINCT VALUE FROM TABLE 2 WHERE KEY = 'COLOR') AND

(TYPE_COUNT = 0 ORTYPE IN (SELECT DISTINCT VALUE FROM TABLE 2 WHERE KEY = TYPE').I have 20-30 attributes like COLOR and TYPE and I need to hard code the SQL like this.

Is there any other better solution. I know there is something called APPLY_FILTER but I am not sure how I will design the filter string from the above table. Thanks in advance !