9.2. More Complex Views
Perhaps one of the most common uses of views is to flatten data — that is, the removal of complexity that we outlined at the beginning of the chapter. Imagine that we are providing a view for management to make it easier to check on sales information. No offense to managers who are readingthis book, but managers who write their own complex queries are still a rather rare breed — even in the information age.
For an example, let's briefly go back to using the AdventureWorks database. Our manager would like to be able to do simple queries that will tell him or her what orders have been placed for what items and how many sold on each order and related pricing information. So, we create a view that the he or she can perform very simple queries on — remember that we are creating this one in AdventureWorks:
USE AdventureWorks GO CREATE VIEW CustomerOrders_vw AS SELECT o.SalesOrderID, o.OrderDate, od.ProductID, p.Name, od.OrderQty, od.UnitPrice, od.LineTotal FROM Sales.SalesOrderHeader AS o JOIN Sales.SalesOrderDetail AS od ON o.SalesOrderID = od.SalesOrderID JOIN Production.Product AS p ON od.ProductID = p.ProductID
Now do a SELECT:
SELECT * FROM CustomerOrders_vw
You wind up with a bunch of rows — over 100,000 — but you also wind up with information that is far simpler for the average manager to comprehend and sort out. What's more, with not that much training, the manager (or whoever the user might be) can get right to the heart of what he or she is looking ...