Converting queries to views, handling IIF using CASE and COALESCE statements

support@ascassociates.biz , Sales Enquiries

Converting queries to views, handling IIF using CASE and COALESCE statements

Access Queries often depend on other Access Queries, often sets of queries can have common elements.

If for reasons of performance qryCalculateRevenue is to be converted to either a Pass-Through Query or View, then qrySelectCustomer will need to be at the very least made into a view with the same name.

You would then have the choice of also converting qryMailingList or living with both an Access Query called qrySelectCustomers and a SQL Server View called qrySelectCustomers.

In applications with several hundred or thousand queries this can become a big task, which is why you could consider only converting Queries which are causing a problem.

Implications For Other Queries

You need to answer several questions in order to scope the work required in attempting to convert a query.

What other queries does the selected query use?
Which components are shared by other sets of queries in the system?
Is the selected query referred to on other queries?

Once you decide to convert a complex query, you will more than likely end up converting a set of queries, or possibly multiple sets.

Significant Areas Of Work

There are some special factors to look at when considering the work required to convert a query.

Does the query, or any dependent components refer to form controls, such as forms!myForm.Country ? (See article on Converting SQL Which Refers To Form Controls Using A Parameter Table)
Does the query have calculations based on further calculations? (See article on Calculations Using Nested Queries)
Does the query use Access specific VBA Functions, or custom functions? (These need converting to equivalent SQL Server Functions, or you can create your own SQL Server functions)
Does the query use IIF functions? (These can be converted to CASE statements or you can use COALESCE Functions)

The remainder of this article expands on the last point, replacing IIF Functions.

Changing IIF To CASE Statements

The following techniques make for a good choice when converting IIF statements, as they follow a very similar structure to IIF structures.

Firstly let us look at a simple Access Query using an IIF Statement

SELECT Customers.CompanyName,
IIf(IsNull([Region]),"No Region Specified",[Region]) AS TheRegion
FROM Customers

The CASE statement can take one of two forms depending upon what you are trying to do, these statements can also be nested like IIF functions.

The example below shows what is known as the Simple CASE structure, which is appropriate when you have a set of possible different values, with different outcomes.

SELECT Customers.CompanyName,
CASE [Region]
WHEN 'BC' THEN 'BC Detected'
WHEN 'SP' THEN 'SP Detected'
ELSE [Region]
END AS TheRegion
FROM Customers

The second example below uses a Searched CASE structure and is of particular use when testing for null.

SELECT Customers.CompanyName,
CASE WHEN [Region] is null
THEN 'No Region Specified'
ELSE [Region]
END AS TheRegion
FROM Customers

Your choice from the above depends upon the detailed syntax of your IIF statements.

Changing IIF To COALESCE Functions

Another very useful function in this context is the COALESCE function, which allows you to select the first non-null value which is very useful in protecting arithmetic calculations. For example the following will either take the field value or when null use a value of 1.

COALESCE([TheField],1)

Using the above example we could have converted the IIF function as follows.

SELECT Customers.CompanyName,
COALESCE( [Region] ,'No Region Specified') AS TheRegion
FROM Customers

Download Code

The sample code can be downloaded from here Access 2000 Database.

Take the strain out of converting SQL with MUST+SQL. Click below to improve your development productivity.

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz

support@ascassociates.biz , Sales Enquiries

sales@ascassociates.biz