A derived table is an in-line SQL SELECT statement in the FROM clause of a query. They are also known as in-line views. Essentially they are non-created views used on the fly. A simple derived table is:
FROM (SELECT *
FROM SourceTable) AS drvd_tbl
In most relational databases, derived tables must be aliased. In the example above, the derived table is aliased as drvd_tbl.
Derived tables are very useful for performing calculations that require averages or division at a granularity different from the source and target tables.
Here’s a table I created in WhereScape RED called stage_orders:
Here it’s source diagram:
When I built the update procedure for stage_orders, I specified the following source table join:
My source data is at invoice line level and includes the customer, product, sales and order dates, sale value, tax and quantity. 21 invoice lines are loaded:
I need to add a condition to only load data for customers who have an average sale price across all orders over $10. I could do this in multiple steps or using a database view, but I’ve decided to use a derived table that retrieves the list of customers with an average sale price across all orders over $10. The derived table is: SELECT customer_code FROM load_order_header load_order_headerJOIN load_order_line load_order_lineON load_order_header.order_number = load_order_line.order_numberGROUP BY customer_codeHAVING AVERAGE(sales_value/quantity) > 10;
In WhereScape RED, all I have to do is add this SQL to the Source Table join like this:
Now only 11 invoice lines are loaded: