Handling Complex Queries

Converting queries that have complex calculations (we are going to show a simple calculation to explain this feature) can be a big issue when moving to SQL Server. Consider the following Access query:

SELECT
    [Order Details].OrderID,
    [Order Details].ProductID,
    [UnitPrice]*[Quantity] AS LinePrice,
    [LinePrice]*0.2 AS LineItemTax,
    [LinePrice]+[LineItemTax] AS FullPrice
FROM [Order Details]

This technique of referring to calculated columns inside other expressions is a very common technique in Access. Unfortunately, SQL Server does not support this, and most migration tools are unable to perform a successful conversion of the SQL.

You have several options when converting this SQL. The simplest option is to restate the calculations in full for each calculated field. The second option is to create SQL Server functions for parts of the calculation, and then modify the calculation to use these functions.

The third option is to create a nested query; you then order the calculations so that they are only referenced by an out level of the nesting. The following code demonstrates the three solutions (ComplexCalculation.sql):

-- Restating the calculations
SELECT
    [Order Details].OrderID,
    [Order Details].ProductID,
    [UnitPrice]*[Quantity] AS LinePrice,
    ([UnitPrice]*[Quantity])*0.2 AS LineItemTax,
    [UnitPrice]*[Quantity]+
    ([UnitPrice]*[Quantity])*0.2 AS FullPrice
FROM [Order Details]
GO
-- using functions
CREATE FUNCTION fn_CalculateLinePrice(@Quantity INT,
				       @Price MONEY)
RETURNS
    DECIMAL(16,4)
AS
BEGIN
    RETURN(@Quantity * @Price)
END
GO
CREATE FUNCTION fn_CalculateTax(@Quantity INT,
				 @Price MONEY)
RETURNS
    DECIMAL(16,4)
AS
BEGIN
    RETURN(@Quantity * @Price *0.2)
END
GO
SELECT
    [Order Details].OrderID,
    [Order Details].ProductID,
    dbo.fn_CalculateLinePrice([Quantity],[UnitPrice]) AS LinePrice,
    dbo.fn_CalculateTax([Quantity],[UnitPrice]) AS LineItemTax,
    dbo.fn_CalculateTax([Quantity],[UnitPrice]) +
    dbo.fn_CalculateLinePrice([Quantity],[UnitPrice]) AS FullPrice
    FROM [Order Details]
GO
-- using a nested query
SELECT
    LinePrice,
    LineItemTax,
    LinePrice + LinePrice AS FullPrice
    FROM
	(SELECT LinePrice, LinePrice* 0.2 AS LineItemTax
    FROM
	(SELECT
	[UnitPrice]*[Quantity] AS LinePrice
	FROM [Order Details]
	) As Nested1
	) As Nested2
GO

At this point, you should appreciate that T-SQL takes writing SQL in Access to a more sophisticated level, and one feature that you don't want to miss is the CASE statement (often used to replace IIF structures). There are two forms for this, SIMPLE and SEARCHED. To illustrate this, the following code creates a very simple table that lists the months in a year, demonstrating the two forms of this statement (CASE.sql):

CREATE TABLE Months(
MonthID 		INT PRIMARY KEY,
TheMonthName 		NVARCHAR(15)
)
GO
INSERT INTO Months(MonthID,TheMonthName)
VALUES(1,'January'),(2,'February'),(3,'March'),(4,'April'),
(5,'May'),(6,'June'),(7,'July'),(8,'August'),
(9,'September'),(10,'October'),(11,'November'),
(12,'December')
GO
-- Simple Case
SELECT MonthID , TheMonthName,
  Simplecase =
  CASE MonthID
    WHEN 1 THEN 'January'
    WHEN 2 THEN 'February'
    WHEN 3 THEN 'March'
    WHEN 4 THEN 'April'
    WHEN 5 THEN 'May'
    WHEN 6 THEN 'June'
    WHEN 7 THEN 'July'
    WHEN 8 THEN 'August'
    WHEN 9 THEN 'September'
    WHEN 10 THEN 'October'
    WHEN 11 THEN 'November'
    WHEN 12 THEN 'December'
  Else 'Unknown'
  END
FROM Months
GO
-- Searched Case
SELECT MonthID , TheMonthName,
  SearchedCase =
  CASE
    WHEN MonthID IN(1,2,3) THEN 'First Quarter'
    WHEN MonthID IN(4,5,6) THEN 'Second Quarter'
    WHEN MonthID IN(7,8,9) THEN 'Third Quarter'
    WHEN MonthID IN(10,11,12) THEN ' Fourth Quarter'
  Else 'Unknown'
  END
FROM Months
GO