0

I am trying to get data from a table whose name is in another table.

select * from (select tab1.value from tab1 join tab2 on tab1.id = tab2.id )

When i try this i get tab1.value instead of the outer select *

Is there a way to get data from the outer select ?

let me break this into two sql statements to make is little easier

1) select tab1.value from tab1 join tab2 on tab1.id = tab2.id

use the tab1.value from above as table name for 2)

2) select * from tab1.value

mata8
  • 1
  • 1
  • 3
  • 2
    you can't do this with plain SQL. You need dynamic SQL for this. –  Apr 07 '16 at 17:36
  • The query is correct. It is showing all the rows from your inner query. If you can explain better what you need, we may help you. – Walter_Ritzel Apr 07 '16 at 17:37
  • The table name for the outer query is stored as a row in one of the tables in the inner query. I need to lookup that value and get data from it – mata8 Apr 07 '16 at 17:42
  • Possible duplicate of [Oracle: Trying to loop thru insert statement using dynamic list of table names](http://stackoverflow.com/questions/26948964/oracle-trying-to-loop-thru-insert-statement-using-dynamic-list-of-table-names) – mustaccio Apr 07 '16 at 17:57
  • Assuming tab1.value is a character string - you do NOT want select * from tab1.value, you want select * from a table whose name is tab1.value. That is not the same thing. If your first table was tab1, the way to select from it is select ... from tab1, NOT from 'tab1' (with tab1 shown as a string). As a_horse... said, you need dynamic sql for this. –  Apr 07 '16 at 19:33

2 Answers2

0

In your query, the only thing the outer query can select is what you obtained from the inner query. If you want the outer select query to do more, you need to give it something more to work with than just the inner query.

rsjaffe
  • 5,600
  • 7
  • 27
  • 39
0

Essentially, "select *" is returning the result set from your inline view. If you want more information from tab1 to display, you need to use that. The truth is you shouldn't really need to do this at all:

select tab1.value, tab1.other_column, tab1.other_column_i_want, etc 
  from tab1
  join tab2 on tab2.id = tab1.id;

There is no need for the inline view. By the way, "SELECT *" is bad practice.

Think of it this way: If I ask you for one key from your keyring, then expect all the keys from your keyring as part of the answer, you would think I had (at best) a screw loose.

You are initially asking for tab1.value. You cannot extract other columns from that result set.

T Gray
  • 712
  • 4
  • 10
  • i want select * from tab1.other_column_i_want – mata8 Apr 07 '16 at 17:45
  • That makes no sense at all. You are telling your outer query "I am ONLY going to supply tab1.value." You cannot extract other columns from that inner query (called an inline view) because they literally do not exist in that result set. So, either you haven't explained what you want or you don't understand the select syntax. "SELECT *" means "give me all the data in all the columns my source contains. Your inner query only contains one column, tab1.value. – T Gray Apr 07 '16 at 17:47
  • tab1.other_column_i_want is a tablename which is stored in tab1 as a row. It is a look to get the table name to get more information. i updated the original thread to make it easier to understand. sorry if the earlier post did not make sense – mata8 Apr 07 '16 at 17:52
  • OK, I understand the problem. Imagine tab1 consists of columns ID, NAME, FAVORITE_COLOR, BEST_MEAT_PRODUCT. "SELECT *" means "get me everything contained in ALL the columns from tab1". The "where" clause limits what my search criteria are. So, "select * from tab1 where BEST_MEAT_PRODUCT='Baloney';" means "select ID, NAME, FAVORITE_COLOR, BEST_MEAT_PRODUCT from tab1 where BEST_MEAT_PRODUCT='Baloney';". – T Gray Apr 07 '16 at 17:53
  • OK - just reread it. This can only be done using a programmatic function. You cannot substitute a value for an object_name in base SQL. – T Gray Apr 07 '16 at 17:54
  • i will give you an example select tab1.value from tab1 join tab2 on tab1.id = tab2.id returns a value lets say dept i want all rows of table dept(nothing from tab1 or tab2) – mata8 Apr 07 '16 at 17:56