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.
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 !