Menu Request Demo

Derived Tables in WhereScape RED

26 March 2008

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   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: 

 first pic

Here it’s source diagram:

Pic 2

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:



Comments 0

Leave a Comment

No comments.