Home » SQL & PL/SQL » SQL & PL/SQL » Derived table access
Derived table access [message #308803] Tue, 25 March 2008 08:46 Go to next message
callimaco0082
Messages: 18
Registered: March 2008
Junior Member
Hi all,

my problem is the following:

in the database I'm using there is a derived table but I can't see it.
I can't find it in all_tables or in all_tab_columns.

I need to read data from this table but if I execute a command like 'select * from <derived_table_name>' an error occurs because the table doesn't exist.

I can't read data from parents table because they are a lot and from many tables (I don't know them all...)

How to reach that derived table? Is there a system table in which are included information also about derived tables?

Thanks to all will help.
Re: Derived table access [message #308807 is a reply to message #308803] Tue, 25 March 2008 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is a "derived table"?

If you can't see a table in all_tables and get this error when you select then this is because you don't have the privilege to query it.
Action: Get the privilege.

Regards
Michel
Re: Derived table access [message #308810 is a reply to message #308803] Tue, 25 March 2008 09:09 Go to previous messageGo to next message
callimaco0082
Messages: 18
Registered: March 2008
Junior Member
Ok.
Thanks Michel...I'll get the privileges.
Re: Derived table access [message #308819 is a reply to message #308807] Tue, 25 March 2008 09:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8627
Registered: November 2002
Location: California, USA
Senior Member
Michel Cadot wrote on Tue, 25 March 2008 06:56
What is a "derived table"?



The term "derived table" is generally used (mostly in non-Oracle databases) to refer to a select statement inside parenthesis that can be assigned an alias. In other words, it is an inline view. For example:

SELECT *
FROM   (SELECT ...
        FROM   ...) alias_for_derived_table;


Since it does not exist prior to running the query, there can be no definition in the data dictionaries.

I have also seen the term "derived table" used to refer to something like:

SELECT alias.* FROM TABLE (some_object_type) alias;

In this case the definition for the object_type can be found in the user_types data dictionary view.




Re: Derived table access [message #308821 is a reply to message #308819] Tue, 25 March 2008 10:14 Go to previous message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the explaination.

Regards
Michel
Previous Topic: need the query to hide the column values
Next Topic: AVG() on a nested table
Goto Forum:
  


Current Time: Mon Dec 05 04:32:36 CST 2016

Total time taken to generate the page: 0.11874 seconds