IN Condition Tricks

Not your type, huh? Change for the better.
I needed to pull some data from our contact database (ichaos) based on ZIP code. The list of codes to use was fairly lengthy and I was concerned about the length of a possible WHERE clause since the data is stored as text to accommodate both the 5-digit and 9-digit US formats (not to mention postal codes from other countries).

Even though the column is stored as text I decided to use the IN() condition to provide the condition. This worked much better than I expected. Not only were the specific matches returned (we used the 5-digit variation in the conditional), but MySQL also returned any variations on the provided condition.

The following SQL statement:

SELECT … WHERE post_code IN (12345);

returns not only the records matching 12345 but also any that match on the first five characters (such as 12345-6789).

The reason this search appears to work appears to do with type conversion (see Type Conversion in Expression Evaluation). Since we are attempting to compare a string to a number the two arguments are converted to floating point numbers by default. To have better control over the comparison we could use the LEFT() function to truncate the zip code to 5 digits, then the CAST() function to control the conversion of the column used in the statement.


Your custom order
While performing my search I noticed another useful tip for the IN() condition in the comments of the SELECT syntax documentation page. If you use an IN() condition in the ORDER BY clause you can specify a custom ordering scheme for a column. So the following statement:

SELECT … ORDER BY %column_name% IN (%value1%, %value2%), %column_name%;

will order the column first by the values specified in the IN() condition followed by the default ordering scheme for the type of data stored in that column.


Update 2006/12/05:
After some further testing I've found that the custom ordering trick doesn't work as I thought. What it does is group the records matching the condition and sort those as a group. It does not sort the values according to the order specified in the parenthesis. If you want specific control of the ordering you'll have to do a separate IN() condition for each value, thus grouping all the records with that value.

What this means is that my previous example will place all records with %value1% and %value2% at the top, but the sorting of those values will be whatever the default is (the order entered in the database unless other sorting options have been specified).

To get the results I had been seeking you have to use something more like the following:

SELECT … ORDER BY %column_name% IN (%value1%), %column_name% IN (%value2%), %column_name%;

Windows 2003 web server returns 503 Service Unavailable

Yes, lots of problems today ... but at least they're all easy to fix.

After setting up my new web sites I was getting a "Service Unavailable" response from the server. Turns out that once you create the sites you have to start the application pool associated with those sites. It's a fairly minor additional step, but it'd be nice if starting a site would start the associated application pool (or at least remind you to do so).

Oh, and don't forget to create/turn on the appropriate entries in the Web Service Extensions folder as well (such as for the asp processor).

Domain literals not supported by IIS in Windows

A little misleading of a title, but it gets the point across nicely. The MMC GUI tools don't support the creation of a domain in the SMTP console. The solution, though, is fairly easy. Shut down IIS and edit the metabase directly. If you're lucky enough to have Windows 2003 it's super-easy since the metabase in in XML format.

The procedure I used was to creat a domain of the literal sans brackets first. This makes it easy to find the appropriate entry in the metabase. Then I edited the metabase to add the brackets back in. Easy.

CDONTS not included in Windows 2003

I was setting up a new server today when I realized that Windows Server 2003 does not include cdonts.dll. We have a number of older scripts that use this function to send out mail. Eventually we'll be moving all our stuff to open source, but in the meantime I'd rather not rewrite these scripts to use cdosys.dll (mainly because it requires more work and I'm lazy). Luckily the solution is easy enough ... install the cdonts.dll file from a Windows 2000 Server installation disk.

Microsoft was kind enough to provide the info I needed:

• Migrate CDONTS (IIS 6.0)
• Windows Server 2003 does not install CDONTS
  

I have actually already run into this, so I'm not sure why I hadn't noted it before.