onotob

I came across an interesting problem at work today. My unit works on the functional prototype of our company’s CRM utility. It’s built in ASP with an MS SQL backend. We’re adding the ability to show the various states of “Status” that an account or product can have. This is anything from simply “Normal” to “Monetary Hold” or “Frozen”. For the most part we change the visual style of anything that is not “Normal” so that the phone rep knows not to discuss that particular product with a customer on the phone. We would also like to stack the “Normal” products at the top and all the rest under that.

The problem is that quite a few of the domain values come before and after “Normal” alphabetically so we can’t just slap an “ORDER BY” clause on the query and be done with it.

After a bit of digging around a colleague of mine came across this solution, which I think is rather elegant. Our query looks something like this:


SELECT *, sort_column =
CASE status WHEN 'Normal' THEN 'A'
ELSE 'B' END
FROM product_table
ORDER BY sort_column ASC


So we create a temporary field called “sort_column” (the equal sign tells the SQL server to create the field) and fill it with values that can be sorted depending on the content of the “status” field.

The CASE statement works just like “switch”:http://www.devguru.com/Technologies/ecmascript/quickref/switch.html in Javascript or “Select Case”:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsstmselectcase.asp in VBScript. The “status” field works as the source to be tested. Based on its values the “sort_column” field is filled with either an “A” or a “B”.

We’ve only tested this on MS SQL Server 7.0.

And we didn’t even get to implement it as the nifty sort was not in the project requirements.

Posted at 12:54 pm.