Q
Problem solve Get help with specific problems with your technologies, process and projects.

Querying complex derived tables

An Oracle user asks SQL expert Rudy Limeback how to run a query when the inner query results come from five different tables.

I have a query similar to the one in the article Optimize SQL query but with the difference that my inner query results come from five different tables rather than just one (store_sales in this case). So can I use "d. " notation in the outer query to refer to these elements for the select? Right now, it's also showing an error: Sql Not properly closed command at the " as d " location in the query. Could you help me with this please?

Yes, your derived table can be any valid query. Just to recap, the query structure from the previous answer looked like this:

select d.foo
  from (
       select ...
         from store_sales
        where ...
       ) as d

A derived table is the result of running the query that is inside the parentheses. More correctly, it's a subquery, because it's inside the main query. Notice that the outer query's SELECT clause uses the "d." prefix on the columns.

You can put any query into parentheses in the FROM clause like this. However, it is usually necessary to name the derived table, and that's done with the AS clause, which assigns a table alias. Some database systems allow you to omit this alias:

select foo
  from (
       select ...
         from store_sales
        where ...
       )

However, you would of course need to use an alias if you wanted to join the derived table to another table in the main query.

Your syntax error might be the result of using the AS keyword in Oracle. For some reason, Oracle doesn't allow it. Try this instead:

select d.foo
  from (
       complex 5-table query here
       ) d

Note the AS keyword is omitted.

This was last published in June 2008

Dig Deeper on Oracle development languages

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close