SQLThoughts: The Virtue of the Virtual Views

The following SQL Puzzle is found in the book APRESS: Advanced Transact –SQL for SQL Server 2000 in page no 71 of Chapter 2.

You are provided with these tables: Orders, OrderDetails, and OrderPayments. Each order in the Orders table can have one or more order parts in the OrderDetails table and zero or more payments in the OrderPayments table. Your task is to write a query that produces order information along with the sum of its order parts and order payments.

The Orders table will look like this:

Ordered custid odate

1 1001 2001−01−18 00:00:00.000

2 1002 2001−02−12 00:00:00.000

The OrderDetails table will look like this:

orderid partno qty

1 101 5

1 102 10

2 101 8

2 102 2

The OrderPayments table will look like this:

orderid paymentno value

1 1 75

1 2 75

2 1 50

2 2 50

The expected output is shown in the following table:

orderid custid odate sum_of_qty sum_of_value

1 1001 2001−01−18 00:00:00.000 15 150

2 1002 2001−02−12 00:00:00.000 10 100

How can you solve this?

To solve this problem you need to write a query with two derived table in SQL Server 2000.

Derived tables are Virtual Tables in SQL Server that are similar to views but differs from views by not storing the definition of it in the Metadata. The Derived Table is a result set of a Query that is used in the FROM Clause of an outer Query and it will be no longer available after the execution outer query. Derived Tables can be nested but may not be co-related.

Let’s go to the solution.

Query listing:1

Select OQ.OrderId,OQ.CustId,OQ.ODate , QtySum,Paysum

From (

Select Orders.OrderId, Orders.CustId,Orders.Odate,

Sum(OD.QTY) as QtySum

From Orders Join OrderDetails OD

On Orders.OrderID = OD.OrderId

Group By Orders.OrderId,Orders.CustId, Orders.Odate

) OQ

Join

( Select O1.OrderID, Sum(op.[value]) as PaySum

from Orders O1

Join OrderPayments OP

on O1.OrderId = Op.Orderid

Group By O1.OrderId ) as OP1

On OQ.OrderID = OP1.OrderId

This query is gives you the desired solution (which is also available in that book at page no 494)

As this book is dealing SQL Server 2000 T-SQL, the author used this derived table way.

Using Derived Table for complex queries having some disadvantages. It reduces the readability of the query and as well as the debugging complex queries (using derived tables) is a big job.

SQL Server 2005 offers a novel way to handle these kinds of problems. Using CTE (Common Table Expressions) you can improve readability, and easily maintain a complex query by building such queries in several blocks.

CTE offers you something that derive tables doesn’t, are, Derived Tables are not referred multiple time in a query, and CTE can be used as recursive queries.

First I show you the CTE way solution to that problem

Query listing:2

With QTYSum as

(Select Ord.OrderId,Sum(OD.Qty) as PartQtySum

From Orders Ord

Join OrderDetails OD

on OD.OrderId = Ord.OrderId

Group By Ord.OrderId

), --Block 1

PaySum As

(Select O.OrderId,Sum([Value]) as PaySum

From Orders O

Join OrderPayments OP

On O.OrderId = OP.OrderId

Group By O.OrderId

) -- Block 2

Select Orders.OrderID, Orders.CustId, Orders.Odate, PartQtySum, PaySum

From Orders

Join Paysum on Orders.OrderId = Paysum.Orderid

Join QTYSum on Orders.ORderId = QTYSum.OrderID

Here, we used the same lines of code, but separating its appearance, which gives us better readability. The OQ alias Select Query Statement in the Query listing:1 was replaced by the Block 1 Query of the Query listing:2 and the OP1 alias Select Query Statement in the Query listing:1 was replaced by the Block 2 Query of the Query listing:2

But this CTE is not used multiple references and recursive queries. Let us see some other example for these options.

Suppose you want to compare each year’s Sales of a particular Employee with his or her previous year’s sales, the CTE can be used as follows.(NorthWind Database’s Orders Table is used for illustration)

With YearToDateSales as

( Select EmployeeId,Year(OrderDate) AS OrderYear,

Count(*) as [Sales]

From dbo.Orders

Where EmployeeId=1

Group by EmployeeId,Year(OrderDate)

)

Select Cur.OrderYear,Cur.Sales , Prv.Sales

From YearToDateSales as Cur

Left Outer Join YearToDateSales Prv

                       on Prv.OrderYear = Cur.OrderYear -1

In this example, a CTE named ‘YearToDateSales‘ was referred two times in the outer query with two different aliases(Cur, Prv). The Employee ID is hard-coded, can be replaced with an argument. Add the following two lines before the With caluse of the CTE.

Declare @empId as int;

set @empID=1;

And replace the ‘Where EmployeeId=1’ with ‘Where EmployeeId=@empID’.

Suppose a CTE is used in batch like the above argumented CTE, the previous statement of With should be terminated by semicolon.

Now for a recursive CTE, consider a query that has to fetch the hierarchy of organisation using its Employee table.

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS

(

SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel

FROM HumanResources.Employee

WHERE ManagerID IS NULL

UNION ALL

SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1