SELECT in FROM clause not working

[lbrechler]

Hi.

I need to perform the following query:

SELECT * from (SELECT s.tablename FROM sourcedef s, attriblist a WHERE a.attribid=1 and a.sourceid = s.sourcedefid)

In both Toad and SQL*Plus, this is returning the value of the table name only, i.e. the results of the inner query.

Is the SQL formatted correctly? Any suggestions?

Thanks.
~Lindsay

 

[Turkbear]

Hi,
Your query asks for all fields returned by the sub-query: The sub-query returns s.tablename and nothing else:
All is working exactly as you requested...

What do you really want to return?

 

[lbrechler]

Well, I want all rows/columns of the table whose name is stored in sourcedef. So I need to select sourcedef.tablename, and use the resulting value (let's say it's "mytablename&quot to do "select * from mytablename"...

 

[lionofthejungle]

HI,

You need to use dynamic SQL for your type of query using a PL/SQL block.

Regards
lionofthejungle

 

[vinperla]

You could also run a script from sql plus
set pagesize 0
set linesize 120

Spool runme.sql
SELECT distinct 'Select * from '||s.tablename ||';'
FROM sourcedef s, attriblist a
WHERE a.attribid=1 and a.sourceid = s.sourcedefid
/
Spool Off
Spool Myresult.txt
@runme.sql
spool off
ed Myresult.txt

Did this very fast. Please check Syntax for what you need.
Vinnie