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.
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.