Table Alias Implementation


The query in Listing 13.5 is an example of the table alias type. The query in the script essentially uses the date dimension table twice, once for the order date (whose alias is order_date_dim) and once for the request delivery date (whose alias is request_delivery_date_dim).

Listing 13.5: Daily sales summary

image from book
 /**********************************************************************/ /*                                                                    */ /* table_alias.sql                                                    */ /*                                                                    */ /**********************************************************************/ SELECT   order_date_dim.date order_date , request_delivery_date_dim.date request_delivery_date , SUM (order_amount) , COUNT(*) FROM   sales_order_fact a , date_dim order_date_dim , date_dim request_delivery_date_dim WHERE     a.order_date_sk = order_date_dim.date_sk AND a.request_delivery_date_sk = request_delivery_date_dim.date_sk GROUP BY   order_date_dim.date , request_delivery_date_dim.date ORDER BY   order_date_dim.date , request_delivery_date_dim.date ; /* end of script                                                      */ 
image from book

You run the query using this command.

 mysql> \. c:\mysql\scripts\table_alias.sql

The result of the query is as follows.

 +-----------+-----------------------+--------------------+----------+ | order_date| request_delivery_date | SUM (order_amount) | COUNT(*) | +-----------+-----------------------+--------------------+----------+ | 2007-03-04| 2007-03-30            |            9500.00 |        3 | +-----------+-----------------------+--------------------+----------+ 1 row in set (0.00 sec)

The output shows the total of the three new orders whose request delivery dates are selected.



Dimensional Data Warehousing with MySQL. A Tutorial
Dimensional Data Warehousing with MySQL: A Tutorial
ISBN: 0975212826
EAN: 2147483647
Year: 2004
Pages: 149

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net