Table Alias Implementation
Table Alias Implementation
The query in Listing 13.5 is an example of the table alias type. The query in the script
Listing 13.5: Daily sales summary
|
/**********************************************************************/ /* */ /* 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 */
|
|
You run the query using this command.
mysql> \. c:\mysql\scripts\table_alias.sql
The result of the query is as
+-----------+-----------------------+--------------------+----------+ 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.
Database View
You implement the second type of date dimension role-playing by creating two database views, one for each of the two dates. You or your
The script in Listing 13.6 can be used to create the views.
Listing 13.6: Creating date views
|
|
/**********************************************************************/ /* */ /* date_views.sql */ /* */ /**********************************************************************/ USE dw; CREATE VIEW order_date_dim ( order_date_sk , order_date , month_name , month , quarter , year , promo_ind , effective_date , expiry_date ) AS SELECT date_sk , date , month_name , month , quarter , year , promo_ind , effective_date , expiry_date FROM date_dim ; CREATE VIEW request_delivery_date_dim ( request_delivery_date_sk , request_delivery_date , month_name , month , quarter , year , promo_ind , effective_date , expiry_date ) AS SELECT date_sk , date , month_name , month , quarter , year , promo_ind , effective_date , expiry_date FROM date_dim ; /* end of script */
|
|
Use this command to execute the script in Listing 13.6 to create the views.
mysql> \. c:\mysql\scripts\date_views.sql
The query in Listing 13.7 uses the two date views to accomplish the same objective as the previous query that uses table alias.
Listing 13.7: Database view role playing
|
|
/**********************************************************************/ /* */ /* database_view.sql */ /* */ /**********************************************************************/ SELECT order_date , request_delivery_date , SUM (order_amount) , COUNT(*) FROM sales_order_fact a , order_date_dim b , request_delivery_date_dim c WHERE a.order_date_sk = b.order_date_sk AND a.request_delivery_date_sk = c.request_delivery_date_sk GROUP BY order_date, request_delivery_date ORDER BY order_date, request_delivery_date ; /* end of script */
|
|
Run the database_view.sql script using this command.
mysql> \. c:\mysql\scripts\database_view.sql
The result should be the same as the result from the previous query.
+-----------+-----------------------+--------------------+----------+ 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)
Similar products
Similar products
- MCSE Planning and Maintaining a Windows Server 2003 Network Infrastructure. Exam 70-293 Study Guide and DVD Training System
Universal group membership external trust - Visual Basic Shell Programming
Namespace extension caling itself - Business Objects(c) The Complete Reference
Business objects number format negative in red - ASP. NET Cookbook
Asp cookbook recipe database - AppleScript. The Definitive Guide
Mac create definitive alias - UNIX. The Complete Reference
Cut and paste command to rearrange fields in unix