Menu Request Demo

Derived Tables in WhereScape RED

Date:
26 March 2008
Author:
Jason Laws

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: 

SELECT *

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: 

 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:

33.JPG

 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:

4.JPG

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:

51.JPG

Now only 11 invoice lines are loaded:

6.JPG

CATEGORIES:

Comments 0

Leave a Comment

No comments.