Query a table and a column name stored in a table

Question

I come here because of a problem that I am not experienced enough to solve, in an Oracle Database. Let me explain:

The tables

I have a table that we'll call Attributes, containing 3 columns : ID, the id of the attribute, -EDIT: Entity_ID as well, the entity it refers to /EDIT-, Table_name, containing the name of the table in which the value of the attribute is stored, and Column_name, containing the name of the column in that table in which is the value is stored.

Every tables referenced in the column Table_name, contains the same column names (such as Value1,Value2, etc..) except for the first one that references another entity (Entity_id), to which the attribute is linked to.

What I am looking for

My goal is to build a query that selects every attribute (based on its id) and its value. But what I don't know is how to query that since the name of table and of the column changes. Is there a way to use variables? But if so, how can I put them in the query so that it automatically changes for each row?

EDIT

EXAMPLE

ATTRIBUTES table
ID         ENTITY_ID      TABLE_NAME   COLUMN_NAME
---------- -------------- ------------ -----------
1          3              Values_A     Value_1  
2          2              Values_B     Value_3
3          2              Values_A     Value_2

VALUES_A table
ENTITY_ID  Value_1        Value_2      Value_3
---------- -------------- ------------ -----------
1          Monday         42           Green
2          Sunday         3000         Blue
3          Wednesday      1            Black

VALUES_B table
ENTITY_ID  Value_1        Value_2      Value_3
---------- -------------- ------------ ------------
1          Tuesday        26           Green
2          Saturday       3            Red
3          Wednesday      15           White

So the result I'm looking for would be:

RESULT:
ID        Value
--------- -----------
1         Wednesday
2         Red
3         3000

Sorry if this is painful to watch, it was as painful to make (didn't find how to format it better)

can you share the actual DDL for ATTRIBUTES as well as sample data and what you want your output to look like? — thatjeffsmith, Apr 24 '18 at 11:58
https://stackoverflow.com/questions/8739203/oracle-query-to-fetch-column-names or http://onewebsql.com/blog/list-all-tables — cSteusloff, Apr 24 '18 at 12:00
@thatjeffsmith I can't share it since it's a bit sensitive data and it wouldn't help since I tried to simplify the situation as best as I could describing only the (biggest) problem I am dealing with. The output should look like a simple query result, such as `SELECT ID, Entity_ID, Value from TABLE` if TABLE was a simple table that just stored the value instead of storing the location of the said — CaptainIK, Apr 24 '18 at 12:06
@CaptainIK then make up some fake rows - make it easier for us to help you and you'll increase your odds ten-fold of getting actionable answers — thatjeffsmith, Apr 24 '18 at 12:07
@cSteusloff So what you're suggesting me would be to fetch all the tables in my database and do like a join based on the name I have stored in Attributes' column Table_name? — CaptainIK, Apr 24 '18 at 12:11
@thatjeffsmith Done. Let me just specify that I didn't chose this type of data architecture ... — CaptainIK, Apr 24 '18 at 12:50

score 2 · Answer 1 · answered Apr 24 '18 at 13:22

2

this is ugly

but it would generate a sql statement that you could run, maybe it is not what you want

select 'select '|| column_name || ' from ' || table_name || ' where entity_id = ' || entity_id || case when id = max_id then '' else ' union all ' end
from 
  (select a.*, max(a.id) over (order by id) max_id 
   from attributes a)

answered Apr 24 '18 at 13:22

Peter M

192
5

Ok, this seems like it could work! But how can I run this statement then? – CaptainIK Apr 24 '18 at 14:02
run this sql (in sql developer or toad or something) -- then get the result, it will be another sql statement -- run that result in another window in sql developer and hopefully it will give you want you want – Peter M Apr 24 '18 at 14:12
When I run this on SQL developer, the result is a table and on each row there's a query, and I have absolutely no idea how to run it again in order to make the final result table? – CaptainIK Apr 24 '18 at 14:34
just select every row -- then copy, then paste it into the worksheet -- each row is a query but see how there is a union all at the end, that will make all of them together one big sql statement – Peter M Apr 24 '18 at 16:22

score 2 · Accepted Answer · answered Apr 24 '18 at 17:18

Using Peter M's query to build the SQL text, and then harnessing the dark power of XML:

create table attributes (id, entity_id, table_name, column_name)
as
select 1, 3, 'VALUES_A', 'VALUE_1' from dual union all
select 2, 2, 'VALUES_B', 'VALUE_3' from dual union all
select 3, 2, 'VALUES_A', 'VALUE_2' from dual;

create table values_a (entity_id, value_1, value_2, value_3)
as
select 1, 'Monday', 42, 'Green' from dual union all
select 2, 'Sunday', 3000, 'Blue' from dual union all
select 3, 'Wednesday', 1, 'Black' from dual;

create table values_b (entity_id, value_1, value_2, value_3)
as
select 1, 'Tuesday', 26, 'Green' from dual union all
select 2, 'Saturday', 3, 'Red' from dual union all
select 3, 'Wednesday', 15, 'White' from dual;

Query:

with queries as
     ( select table_name, column_name, entity_id
            , 'select '|| column_name || ' as c from ' || table_name ||
             ' where entity_id = ' || entity_id ||
              case
                  when id = max_id then ''
                  else ' union all '
              end as sqltext
       from 
           ( select a.*, max(a.id) over (order by id) max_id from attributes a ) )
select table_name, column_name, entity_id
     , extractvalue(xmltype(dbms_xmlgen.getxml(sqltext)),'/ROWSET/ROW/C') as sql_result
from   queries;

Results:

TABLE_NAME COLUMN_NAME  ENTITY_ID SQL_RESULT
---------- ----------- ---------- ---------------------------------------------------
VALUES_A   VALUE_1              3 Wednesday
VALUES_B   VALUE_3              2 Red
VALUES_A   VALUE_2              2 3000

Thank you very much (and @Peter M for the basis of it) for your answer! It works perfectly fine on the example. Now all I have to do is to adapt it to my problem (that includes multiple joins but that should be alright). I didn't know anything about using xml like this, and this "with _ as __ select ", I think I'll look into this for the future. Thanks again ! — CaptainIK, Apr 25 '18 at 09:57
I should add that [`extractvalue` is deprecated as of 11gR2](https://docs.oracle.com/database/121/ADXDB/changes.htm#ADXDB-GUID-4D46D8F7-2ACC-49D5-863D-7F16BF84EBCD), but I'm afraid I don't know enough XML to be able to change it to the currently favoured syntax. The manual suggests `XMLTable` or `XMLCast` and `XMLQuery`, but those are not drop-in replacements and would thus seem to be less useful than the deprecated function, but that's XML for you. — William Robertson, Jun 14 '18 at 16:51

Query a table and a column name stored in a table

The tables

What I am looking for

EXAMPLE

2 Answers2

Linked

Related