Ask The Oracle Expert: Questions & Answers

Querying complex derived tables

Querying complex derived tables

By  Rudy Limeback, SQL Consultant, r937.com

SearchOracle.com

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.