I'm writing a simple diagnostic query then attempting to execute it in the Oracle 10g SQL Scratchpad. EDIT: It will not be used in code. I'm nesting a simple "Select *" and it's giving me errors.
In the SQL Scratchpad for Oracle 10g Enterprise Manager Console, this statement runs fine.
SELECT * FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id and sql.sql_text <> ' '
If I try to wrap that up in Select * from () tb2 I get an error, "ORA-00918: Column Ambiguously Defined". I didn't think that could ever happen with this kind of statement so I am a bit confused.
select * from
(SELECT * FROM v$session sess, v$sql sql WHERE sql.sql_id(+) = sess.sql_id and sql.sql_text <> ' ')
tb2
You should always be able to select * from the result set of another select * statement using this structure as far as I'm aware... right?
Is Oracle/10g/the scratchpad trying to force me to accept a certain syntactic structure to prevent excessive nesting? Is this a bug in scratchpad or something about how oracle works?
When Oracle parses a SELECT *, it expands it out to an actual list of the columns to be selected. Since your inline view contains two columns named SQL_ID, this results in an ambiguous reference.
Interestingly, using ANSI join syntax seems to cause it to alias the duplicate column names automatically, and therefore avoids the error.
select * from
(select * from v$session sess left outer join v$sql sql on sql.sql_id=sess.sql_id and sql.sql_text <> ' ')
Incidentally, it's not clear to me why you chose that condition on sql_text. I don't expect that column would ever contain a single space. Are you really trying to filter out NULLs? If so, why use an outer join at all?