SQLServerCentral Article

Converting Access Queries with iff() and DLookup() to SQL Server

,

Recently I worked on a project where I copied the tables and some of the SQL queries from an Access 2007 database to a SQL Server 2005 database. Most of the queries easily transcribed from Access to SQL Server. After upsizing the tables from Access to SQL Server Express, I simply ran the Access queries in a SQL Management Studio Express query window.

However, two functions, iif() and DLookup(),   were inserted into some of my project’s Access SQL queries. These added some excitement to my port, since neither is supported by SQL Server.

This article provides an introduction to these two Access functions, and demonstrates how to convert an Access SQL statement that uses these functions into T-SQL that will work on your SQL Server.

To illustrate these functions, let’s use some sample data that is a list of 8 pets. Each pet has 6 characteristics. The data is listed in the table below.

TypeHasFurBarksNumLegsNameColor
DogYesYes4Spikeblack/brown
DogYesYes4Rexblack/white
SnakeNoNo0Slithergreen
PigNoNo4Wilburpink
CatYesNo4Fluffyblack/white
CatYesNo4Huntertabby
MonkeyYesNo2Mr. Bigglesdark brown
IguanaNoNo4Godzillalime green

To build these data tables in Access, create a module and insert the following code. You can call the CreateDatabase() subroutine by building a form with a button to invoke this function.

Option Compare Database

Option Explicit

Public Sub CreateDatabase()

    ' ---------------------------------------

    On Error GoTo CreateDatabaseError

    Dim sqlCommand As String

    On Error GoTo DropDatabaseError

    sqlCommand = "DROP TABLE Pets"

    RunSQLCommand sqlCommand

DropDatabaseError:

    sqlCommand = "CREATE TABLE Pets " _

        & "(Type TEXT, HasFur YESNO, " _

        & "Barks YESNO, NumLegs SINGLE, Name TEXT, Color TEXT);"

    RunSQLCommand sqlCommand

    sqlCommand = "INSERT INTO Pets VALUES ('Dog', Yes, Yes, 4, 'Spike', 'black/brown')"

    RunSQLCommand sqlCommand

    sqlCommand = "INSERT INTO Pets VALUES ('Dog', Yes, Yes, 4, 'Rex', 'black/white')"

    RunSQLCommand sqlCommand

    sqlCommand = "INSERT INTO Pets VALUES ('Snake', No, No, 0, 'Slither', 'green')"

    RunSQLCommand sqlCommand

    sqlCommand = "INSERT INTO Pets VALUES ('Pig', No, No, 4, 'Wilbur', 'pink')"

    RunSQLCommand sqlCommand

    sqlCommand = "INSERT INTO Pets VALUES ('Cat', Yes, No, 4, 'Fluffy', 'black/white')"

    RunSQLCommand sqlCommand

    sqlCommand = "INSERT INTO Pets VALUES ('Cat', Yes, No, 4, 'Hunter', 'tabby')"

    RunSQLCommand sqlCommand

    sqlCommand = "INSERT INTO Pets VALUES ('Monkey', Yes, No, 2, 'Mr. Biggles', 'dark brown')"

    RunSQLCommand sqlCommand

    sqlCommand = "INSERT INTO Pets VALUES ('Iguana', No, No, 4, 'Godzilla', 'lime green')"

    RunSQLCommand sqlCommand

    Exit Sub

CreateDatabaseError:

    ' You only get here if an error occurred.

    ' Show the error.

    MsgBox Err.Description

End Sub

Private Sub RunSQLCommand(ByVal sqlCmd As String)

On Error GoTo RunSQLCommandError

    Dim db As DAO.Database

    ' -------------------------------------

    Set db = CurrentDb

    ' Run the command.

    db.Execute sqlCmd

    Exit Sub

RunSQLCommandError:

    ' You only get here if an error occurred.

    ' Show the error.

    MsgBox Err.Description

End Sub

Since I copied my table data from Access to SQL Server, I created an ODBC connection, and upsized the Pets table to SQL Server. However, you can run the following T-SQL statements in a SQL query window to create the Pets table, and populate it with data.

CREATE TABLE [Pets](

      [Type] [nvarchar](255)NULL,

      [HasFur] [bit] NULL DEFAULT((0)),

      [Barks] [bit] NULL DEFAULT((0)),

      [NumLegs] [real] NULL,

      [Name] [nvarchar](255)NULL,

      [Color] [nvarchar](255)NULL

);

GO

INSERT INTO Pets VALUES('Dog','True', 'True', 4, 'Spike','black/brown');

INSERT INTO Pets VALUES('Dog','True', 'True', 4, 'Rex','black/white');

INSERT INTO Pets VALUES('Snake','False', 'False', 0, 'Slither','green');

INSERT INTO Pets VALUES('Pig','False', 'False', 4, 'Wilbur','pink');

INSERT INTO Pets VALUES('Cat','True', 'False', 4, 'Fluffy','black/white');

INSERT INTO Pets VALUES('Cat','True', 'False', 4, 'Hunter','tabby');

INSERT INTO Pets VALUES('Monkey','True', 'False', 2, 'Mr. Biggles', 'dark brown');

INSERT INTO Pets VALUES('Iguana','False', 'False', 4, 'Godzilla','lime green');

GO

Once the data is available in the Access and SQL Server platforms you can try out the iif() and Dlookup() functions in Access, and their functional equivalents  in SQL Server.

The iif() Function

The iif() function allows you to insert inline conditional statements into your Access SQL queries. The format is iif(, , ). The function design is straightforward. If the evaluates to true, the is performed. Otherwise, the is invoked.

Let’s start with a simple example using iif(). For any cats in the Pets table, list their sound in this new column (named Sound) as “Meow”.

SELECT Name, IIF(Type = 'Cat', 'Meow', '') As Sound From Pets

Given the domain of the table Pets, two animals are cats, Fluffy and Hunter.

The result of the Access query is shown below.

In SQL Server, conditional logic can be implemented with a CASE statement. Below is the Access statement migrated to T-SQL. The CASE uses the Type of pet as the condition. A ‘Cat’ type returns the Sound ‘Meow’. Otherwise nothing is shown.

SELECT Name,

       Sound =CASE Type

                  WHEN 'Cat' THEN'Meow'

                  ELSE ''

               END

FROM Pets

The results are displayed below.

This diagram illustrates the conversion. Replace IIF with CASE, the becomes the WHEN clause, and the is the ELSE clause.

The problem with the iif() function and the SELECT … CASE statement is that the former is supported only in Access, and the latter is supported only in SQL Server. Is there a statement that would work for both platforms?

Using the set diagrams, notice that the result of this conditional query is the union between the set of pets that “Meow” and those that don’t.

The SQL ‘UNION ALL’ statement below will run correctly on both Access and SQL Server.  

SELECT Name, 'Meow' As Sound

FROM Pets

WHERE Type = 'Cat'

UNION ALL

SELECT Name, ' ' As Sound

FROM Pets

WHERE Type <> 'Cat';

 The two sets could also be joined using a ‘UNION’ statement, but, since the members are already distinct, the faster ‘UNION ALL’ is a better choice. A UNION, by definition, must eliminate all duplicate rows as opposed to UNION ALL.

The above was an easy example. Although the cats may meow, the other pets also make sounds. The dogs bark. The snake hisses. The pig oinks. Mr Biggles, the monkey, likes to scream, “Eek-eek.”  The only quiet pet of the lot is Godzilla, the iguana.

An Access query could represent this cacophony with the following query.

SELECT Name, IIF(Barks, 'Ruff Ruff', IIF(Type = 'Cat', 'Meow', IIF(Type = 'Snake', 'Hiss', IIF(Type ='Monkey', 'Eek Eek', IIF(Type = 'Pig', 'Oink', ''))))) As Sound From Pets

The resultant table would be.

Converting this to a SQL Server CASE statement would result in this query below. For complex logic, the CASE statement is much easier to read.

SELECT Name,

       Sound =CASE Barks

            WHEN'True' THEN 'Ruff Ruff'

                        ELSE CaseType

                             WHEN 'Cat' THEN'Meow'

                             WHEN 'Snake' THEN'Hiss'

                             WHEN 'Pig' THEN'Oink'

                             WHEN 'Monkey' THEN'Eek Eek'

                             ELSE ' '

                        END

            END

FROM Pets;

Notice that the Barks attribute, which is a Yes/No type in Access, becomes a bit type upon migration to SQL Server.

Hence, in the SQL Server query you will see Barks being compared to ‘True’, whereas in the Access query the Yes/No data type allow you to use just Barks.

 Using the diagram above as a guide, you can convert the logic of these queries to a UNION ALL. Below is the SQL Server query using a mass of UNION ALL’s.

SELECT Name, 'Ruff Ruff' As Sound

FROM Pets

WHERE Barks = 'True'

UNION ALL

SELECT Name, 'Meow' As Sound

FROM Pets

WHERE Type = 'Cat'

UNION ALL

SELECT Name, 'Hiss' As Sound

FROM Pets

WHERE Type = 'Snake'

UNION ALL

SELECT Name, 'Oink' As Sound

FROM Pets

WHERE Type = 'Pig'

UNION ALL

SELECT Name, 'Eek Eek' As Sound

FROM Pets

WHERE Type = 'Monkey'

UNION ALL

SELECT Name, ''As Sound

FROM Pets

WHERE Type NOT IN

    (SELECT Type

    FROM Pets

    WHERE Barks = 'True' OR

    Type = 'Cat'OR

    Type = 'Snake'OR

    Type = 'Pig'OR

    Type ='Monkey')

The last SELECT covers the case of any quiet pets. If you added Sally, a snail and silent pet, the logic for the UNION query would still work.  For the above query to run in Access 2007, you need to change Barks = ‘True’ to just Barks.

Execution times of CASE versus UNION and UNION ALL's on SQL Server
For data set of 8 or 9 pets, the difference in execution times between the CASE and the UNION or UNION ALL SQL queries is negligible. Thus, I created the following stored procedure, uspLoadPetsTable , which simply inserts the same 10 pet types with unique pet names, into the Pets table. The number of insertions of this group of ten pets is controlled by the input variable, NVal.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE uspLoadPetsTable

      -- Add the parameters for the stored procedure here

      @NVal int= 0

AS

BEGIN

    DELETE FROM Pets;

    DECLARE @count int

      SET @count = 1

    WHILE @count <= @NVal

    BEGIN

       INSERTINTO Pets VALUES ('Dog','True', 'True', 4, 'Spike' +CONVERT(nvarchar(5), @count) , 'black/brown');

       INSERTINTO Pets VALUES ('Dog','True', 'True', 4, 'Rex' +CONVERT(nvarchar(5),@count),'black/white');

       INSERTINTO Pets VALUES ('Snake','False', 'False', 0, 'Slither' +CONVERT(nvarchar(5),@count),'green');

       INSERTINTO Pets VALUES ('Pig','False', 'False', 4, 'Wilbur' +CONVERT(nvarchar(5),@count),'pink');

       INSERTINTO Pets VALUES ('Cat','True', 'False', 4, 'Fluffy' +CONVERT(nvarchar(5),@count),'black/white');

       INSERTINTO Pets VALUES ('Cat','True', 'False', 4, 'Hunter' +CONVERT(nvarchar(5),@count),'tabby');

       INSERTINTO Pets VALUES ('Monkey','True', 'False', 2, 'Mr. Biggles' + CONVERT(nvarchar(5),@count),'dark brown');

       INSERT INTO Pets VALUES ('Iguana','False', 'False', 4, 'Godzilla' +CONVERT(nvarchar(5),@count),'lime green');

        INSERT INTO Pets VALUES ('Snail','False', 'False', 0, 'Sally' +CONVERT(nvarchar(5),@count),'light brown');

       INSERTINTO Pets VALUES ('Goldfish','False', 'False', 0, 'Goldfinger' +CONVERT(nvarchar(5),@count),'orange');

       SET @count = @count + 1

    END

END

GO

To get a base line on the execution time for 10000 records in the Pets table I ran the following. First, I called the stored procedure uspLoadPetsTable with the parameter 1000. This created 10000 records in the Pets table.

EXECUTE uspLoadPetsTable 1000;

GO

CHECKPOINT

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

GO

SET STATISTICS TIME ON

GO

SELECT Name,

       Sound =CASE Barks

            WHEN'True' THEN 'Ruff Ruff'

                        ELSE CaseType

                             WHEN 'Cat' THEN'Meow'

                             WHEN 'Snake' THEN'Hiss'

                             WHEN 'Pig' THEN'Oink'

                             WHEN 'Monkey' THEN'Eek Eek'

                             ELSE ' '

                        END

            END

FROM Pets;

GO

SET STATISTICS TIME OFF

CHECKPOINT

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

GO

SET STATISTICS TIME ON

GO

SELECT Name, 'Ruff Ruff' As Sound

FROM Pets

WHERE Barks = 'True'

UNION ALL

SELECT Name, 'Meow' As Sound

FROM Pets

WHERE Type = 'Cat'

UNION ALL

SELECT Name, 'Hiss' As Sound

FROM Pets

WHERE Type = 'Snake'

UNION ALL

SELECT Name, 'Oink' As Sound

FROM Pets

WHERE Type = 'Pig'

UNION ALL

SELECT Name, 'Eek Eek' As Sound

FROM Pets

WHERE Type = 'Monkey'

UNION ALL

SELECT Name, ''As Sound

FROM Pets

WHERE Type NOT IN

    (SELECT Type

    FROM Pets

    WHERE Barks = 'True' OR

    Type = 'Cat'OR

    Type = 'Snake'OR

    Type = 'Pig'OR

    Type = 'Monkey');

GO

SET STATISTICS TIME OFF

CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
SET STATISTICS TIME ON
GO
SELECT Name, 'Ruff Ruff' As Sound
FROM Pets
WHERE Barks = 'True'
UNION
SELECT Name, 'Meow' As Sound
FROM Pets
WHERE Type = 'Cat'
UNION
SELECT Name, 'Hiss' As Sound
FROM Pets
WHERE Type = 'Snake'
UNION
SELECT Name, 'Oink' As Sound
FROM Pets
WHERE Type = 'Pig'
UNION
SELECT Name, 'Eek Eek' As Sound
FROM Pets
WHERE Type = 'Monkey'
UNION
SELECT Name, '' As Sound
FROM Pets
WHERE Type NOT IN
(SELECT Type
FROM Pets
WHERE Barks = 'True' OR
Type = 'Cat' OR
Type = 'Snake' OR
Type = 'Pig' OR
Type = 'Monkey');
GO
SET STATISTICS TIME OFF

After building the table, the subsequent three commands were preparation for gathering clean statistics about the query execution. The CHECKPOINT writes all dirty pages for the current database to disk. DBCC DROPCLEANBUFFERS removes all the clean buffers. Finally, DBCC FREEPROCCACHE cleans out the query plan cache. Bracketing each query - the CASE, UNION ALL and UNION ones - are calls turning STATISTICS TIME on and off.
For a Pets table with 10000 records, on my humble workstation, the execution of the CASE query was constently 30% faster than the UNION ALL query. In turn, the execution of UNION ALL was less than 10% faster than the UNION query. When I increased the number of pet records to 100000 (EXECUTE uspLoadPetsTable 10000), the CASE query execution was almost 50% faster than either the UNION ALL or UNION queries.
The time to parse and compile the CASE query was from 1 to 5 ms on my workstation, and it was over 10 times longer for the labyrinthine UNION or UNION ALL queries. You pay a performance penalty on both ends - compilation and execution - for the UNION's.

If you want performance, use CASE statements.

What happens if you add a primary key to the Pets table in SQL Server?

Based on how the Pets table is being used, you could consider the Name field to be a primary key, since the pet's name is never null and always unique.Thus, I ran the two following lines to make Name a primary key.

ALTER TABLE Pets ALTER COLUMN Name NVARCHAR(255) NOT NULL;
ALTER TABLE Pets ADD PRIMARY KEY (Name);

Then I repeated the tests using the data generated by the uspLoadPetsTable stored procedure. I used both a table with 10000 and 100000 records. The parse and compilation times for the UNION queries remained much higher than the CASE query, but the execution times between the CASE and UNION's differed by 20% or less. The UNION ALL query was only 1-5% faster than the UNION query.
Adding a primary key did affect the execution speed of the CASE query, however, it reduced the execution time for the UNION's queries by 30% to 50%.
Once again, the CASE statement is the fastest. If you want to use bulky UNION queries, then add a primary key.

The DLookup Function

The syntax for DLookup() is defined by the Microsoft Access 2007 Developers’ Guide as DLookup(, , ). This domain aggregate function, DLookup(),  returns the value of a field for a single record in a table or query.

For example, DLookup is used below to find the name of the pig in the Pets table.

SELECT   DLookup("Name", "Pets", "Type='Pig'") As [Piggy Name]

The domain is the Pets table, and the criterion is that the Type of pet is a pig.

The result Wilbur, as shown is the following.

The domain for DLookup does not have to be a table. It can be the result of another query. Using Access 2007, create a query that contains the following SQL statement:

SELECT Name, Type FROM Pets Where NumLegs >=4

Name this query LegsQuery. This will be the domain for a DLookup function query. The result of this LegsQuery is the Name and Type of all pets that have 4 or more legs.

Create a DLookup() query with the following SQL.

SELECT DLookup("Name","LegsQuery","Type='Pig'") AS [Piggy Name];

Notice that the second parameter, the domain, “LegsQuery”, is not a table but a reference to another query. The quotation marks around the query name are required.

To demonstrate what occurs when the DLookup() function finds two or more matching criteria, invoke it using the Pets table with the condition Color=’black/white’.

From the list of pets, you know that there are two black and white animals: Rex and Fluffy.

If you use invoke the following Access SQL query using DLookup(),

SELECT   DLookup("Name", "Pets", "Color='black/white'") As [Black White]

The resulting table has a single column, Black White, with the name of just one of the pets, Rex. Thus, the DLookup() function returned a subset of black and white pets.

What if you decided to convert the DLookup() function into a SELECT statement with a WHERE clause?

SELECT Name As [Black White] FROM Pets WHERE Color="black/white"

You will have two rows in your result. This matches the expected result. It shows both black and white animals, Rex and Fluffy.

In order to get the same result using SQL as the DLookup() invocation, you must include the FIRST() function. This would just return one row, Rex.

SELECT FIRST(Name) As [Black White]  FROM Pets WHERE Color="black/white"

The conversion from a query with a DLookup() call to SQL is shown in the diagram below.

In general, DLookup(, , ) can be changed to SELECT FIRST FROM WHERE .

Conclusion

Using the simple Pets database, you learned how to invoke the iif() and DLookup() functions inside of Access 2007 queries. You also were shown how to convert these functions to T-SQL that could be invoked on SQL Server 2005. For the case of iif(), you were shown two methods of conversion: one used the CASE construct and the other two used the UNION's construct. Finally, the execution speed of the CASE query, the UNION and the UNION ALL query were discussed, with the CASE clearly being the faster.

Rate

2.92 (38)

You rated this post out of 5. Change rating

Share

Share

Rate

2.92 (38)

You rated this post out of 5. Change rating