SQL: Case, Switch and Data Models

I was trying to remember how to do a case statement in an MS-Access Query. Having worked mostly in SQL Server the last while I forgot that MS-Access uses the switch keyword instead of case. The syntax is very similar.

A case or switch statement in SQL allows you to translate a specific value of a field into another value. It is often used to translate something meaningless like an enum or numeric identifier into something readable. It is often used in views or reports.

In the following example the MSAccess switch statement is used to translate an enumeration field into readable text:

 SELECT switch([T_Member.FamilyTypeId]=1,"parent",[T_Member.FamilyTypeId]=2,"dependent") AS SomeFieldName FROM SomeTable

SQL Server Books Online (2005) shows a simple example of the case statement:

USE AdventureWorks2008R2;
GO
SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO

Now that I've shown you how to do these statements properly, I'm going to argue why you should never use them.

Almost always a case statement means the data model is incomplete. A case statement basically inserts ad hoc lookup tables randomly though-out your SQL statements. As good designers will say, hard-coded text values or numeric literals in any type of code are a maintenance nightmare. How hard is it to extend the data model to provide text definitions of the things you are translating with a case statement?

I feel the same way about the use of case statements in programming languages. The logic is static and cannot be changed except through rebuilding and redeploying. Adding an additional case label to a statement is out of reach for the system administrator, whether SQL or code.

Case or switch fields are not editable by the user. In some cases views that use them can make the entire row not editable. But almost all GUIs support editing of foreign keys through drop downs boxes.

I would argue that case and switch statements should never be used in SQL statements, and focus instead on complete data models, with proper relationships.

The Weird Syntax of SQL DELETE From a JOIN

It is perfectly legal in most SQL dialects to DELETE records that come from a JOIN clause. However, I never remember the syntax because its kind of strange and I don't do it often.

I wrote the following example in SQL Server 2005:

delete from arledger 
from arledger
inner join arinvoice on arinvoice.arinvoiceid = arledger.invoicenumber
where arinvoice.brokersaleid = 2259

Nothing complicated. Just note line 2 where the FROM clause is repeated.

SQL Update with JOIN

I was trying to remember how to do an SQL UPDATE statement using fields from a JOIN. Its one of those things I don't do very often, and the syntax is not exactly intuitive. Its a good example of the power of SQL.

The general template for such a statement is:

UPDATE [target table]
SET clause
FROM [target table]
JOIN clause
WHERE clause

In my case I had two tables. The listing table contained items for sale and the pertinent fields were Price and Commission, both money fields. The CommissionRate table has the same fields. The goal was to update the Listing.Commission field from the CommissionRate table, by joining on the Price fields.

The UPDATE statement ended up as:

UPDATE Listing
SET Listing.Commission = CommissionRate.Commission
FROM Listing
INNER JOIN CommissionRate ON Listing.Price = CommissionRate.SaleAmount
WHERE Listing.Commission <> CommissionRate.Commission

Refactoring:

The Commission.SaleAmount field needs to be renamed to Commission.Price to be consistent within the model

The WHERE clause should include a condition to NOT apply the update to certain records that should not be bulk updated.