SAP HANA Table Name Matches a Column Value in another Table

[former_member337435]

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 !

[former_member337435]

I apologize that the table is showing concatenated values rather than like a table.

[former_member337435]

TABLE1
PRODUCT COLOR TYPE QUANTITY
P1 GREEN TOY 10
P1 RED KITCHEN 10
P2 GREEN DINING 20
P3 RED TOY 30
P4 VIOLET TOY 40
P5 BLUE TOY 50

TABLE2
USERID KEY VALUE
U1 COLOR GREEN
U1 COLOR BLUE
U1 TYPE TOY
U2 COLOR RED
U2 COLOR BLUE

I have redone the table because in the original question it did not show up right.

[vinita_kasliwal]

Hey

I am not sure if I understand completely

Can you not use inner join and 1 select statement?

Something like below:

SELECT FROM TABLE 1 inner join table 2 ?

or you could use apply filter as below?

lt_table2 = select * from :lt_table1 ORDER BY COLOR TYPE

Regards

Vinita

[former_member337435]

Hey Vinita,

You cannot do an inner join. The table column name is the table column value of another table . Unfortunately the table that I pasted with values is not showing up

Thanks,

Lalitha