SQL Server Club
Home Articles Essential Guides Blogs Links Twitter Facebook Join Contact About
Solving Complex Query Calculations Using Nested Queries in SQL Server
Microsoft MVP Andy Couch About the Author

Andrew is a founding member of the UK Access user group and has worked as magazine editor, guest speaker, trainer and run the web sites for the Access and UK Office user groups.

For the past 15 years he has worked with a variety of databases and client-server developments but his current specialty is in migrating large Access databases from Access back-ends to SQL Server. Over the past ten years he and others have been using their own migration tool, which they have now launched as a commercial product at Upsizing.co.uk.

Andrew is a Microsoft MVP and you can follow him on Twitter @Upsizing.
Send to a Friend Printer Friendly Version
The fact that Access allows you to easily construct calculated fields based on other calculated fields means that you can produce very complex calculations with great ease. Unfortunately SQL Server will not allow you to use this technique. If faced with a poorly performing Access Query that uses multiple layered queries, complex calculations and lots of IIF logic in the queries, then you can be facing a bit of a nightmare to convert the SQL to a server-side query written in T-SQL.

This example uses the [Order Details] table in the Northwind database.

Below we have an Access query, which we shall assume is giving very poor performance, and needs to be converted to a pass-through query.

Problem Converting Calculations

The example shown here is very simple, yet you will soon see how it can become very complicated

SELECT [Order Details].UnitPrice, [Order Details].Quantity,
[UnitPrice]*[Quantity] AS LinePrice, 0.175*[LinePrice] AS VAT,
[LinePrice]+[VAT] AS TotalPrice
FROM [Order Details]

This is because SQL Server does not allow calculated fields to refer to other calculations. You could try the following restating each calculation, but for complex calculations this can be very difficult.

SELECT [Order Details].UnitPrice, [Order Details].Quantity,
[UnitPrice]*[Quantity] AS LinePrice,
0.175*[Quantity] *[UnitPrice] AS VAT,
[Quantity] *[UnitPrice]+ 0.175*[Quantity] *[UnitPrice] AS TotalPrice
FROM [Order Details]

Possible Solutions

In Access it is common practice to design a query which uses another query, this process of layering queries on top of queries can be reproduced using views in SQL Server. Views have more limitations than Access queries but breaking a query down into a series of steps layered on top of each other is one possible solution. Although in a complex application this could involve making what already could be a sequence of views into an even greater layered sequence of views. So what other alternatives are there?

Another alternative is to use SQL Server functions, building functions which use other functions, and if you have time this could also provide a possible solution.

A third option would be to utilise joins, below is a partial solution to our problems, but the need here to specify the join criteria can again lead to unwanted complexity.

SELECT od.[OrderId], od.[ProductId],od.UnitPrice, od.Quantity,LinePrice, 0.175*[LinePrice] AS VAT
FROM [Order Details] AS od
(SELECT OrderId, ProductId,UnitPrice, Quantity, [UnitPrice]*[Quantity] AS LinePrice
FROM [Order Details] ) AS JoinedQuery
ON od.[OrderId] = JoinedQuery.[OrderId]
AND od.[ProductId] = JoinedQuery.[ProductId]

The final option is to use a Nested Queries, I attribute this to having spent a fair amount of time with one of my colleagues Brad Barton who first introduced this to me as his preferred technique when working with IBM systems. Nested Queries are a very much underused technique in SQL Server.

Nested Queries

Whilst subqueries allow a query to be injected into another queries WHERE clause, nested queries allow a query to be injected into another queries FROM clause. This is an extremely elegant method for solving problems which require data to have complex calculations and summaries in a single step.

The example below goes beyond the join solution, to give a full solution based on a three level query. The only syntactical issue is that at each level the query must be given a name using an AS clause.

SELECT *,[LinePrice]+[VAT] AS TotalPrice FROM
(SELECT *, 0.175*[LinePrice] AS VAT FROM
(SELECT UnitPrice, Quantity, [UnitPrice]*[Quantity] AS LinePrice
FROM [Order Details]
) AS InnerQuery
) AS NextLevel

I hope you will agree, that nested queries can provide an extremely elegant method for solving complex problems. In particular when dealing with systems where you are not allowed to create server-side views and objects, nested queries are an invaluable tool in a developer's pocket.

MUST+SQL automatically translates your Access queries into Views and Stored Procedures, converting complex calculations to Nested Queries.


Send to a Friend Printer Friendly Version
Top of Page
© Norb Technologies 2007-2010 Privacy Policy Norb Technologies Devdex Feedback Home
Feedback Form