Fix: "Field references in ORDER BY clause must appear in the select list if SELECT DISTINCT is specified."

After upgrading a Java batch application with a Progress/OpenEdge back end to use a new JDBC driver version this morning, while testing the upgraded application to check for any issues with using the new driver, the app generated a SQL error: "[DataDirect][OpenEdge JDBC Driver][OpenEdge] Field references in ORDER BY clause must appear in the select list if SELECT DISTINCT is specified. (13637)".

I googled for that error message and the search didn't come up with any results, so I figured I'd blog the solution briefly here. It turns out the error message in this case is actually pretty good, as SQL datasource error messages go. Given a query that involves a SELECT DISTINCT, a JOIN, and a ORDER BY, like this one:

SELECT DISTINCT a.customer_id
 FROM users a
 INNER JOIN sales b  
 ON a.customer_id = b.customer_id
 ORDER BY b.customer_id 

The problem is that the field reference in the SELECT clause and the reference in the ORDER BY clause don't match. In this case, the SELECT clause references table "a" and the ORDER BY clause references table "b".

The fix is to change the query such that both references are the same:

SELECT DISTINCT a.customer_id
 FROM users a
 INNER JOIN sales b  
 ON a.customer_id = b.customer_id
 ORDER BY a.customer_id 

To double-check my example, I tested the first simple query above briefly in Microsoft Access, and it turns out that Access doesn't like the field mismatch either. The Access error message was "ORDER BY clause (...) conflicts with DISTINCT."

Schneider's Eleven - New skin for Windows Media Player

Over the past week, I've created a new skin (theme) for Windows Media Player. I call it "Schneider's Eleven" -- more on that below.

For a while now, I've wanted a skin for Windows Media Player that meets the following two criteria:

1. Unobtrusive. I wanted something that I could leave visible in a corner of the screen all the time which wouldn't draw my eye with scrolling text or other graphical animations while I'm concentrating on other tasks.
2. Informative. Many "minimal" skins do not display the album, artist, and track name of the currently playing track, or alternate between displaying those values; I wanted a skin where all of those values would be visible at a glance.

None of the default skins included with Windows Media Player meet these criteria, nor does the main Windows Media Player 11 interface in its "compact mode". I'd poked around on several occasions for such a skin, but I couldn't find one on Microsoft's official skins site or elsewhere.

I did a little reading about Windows Media Player skins, and found that WMP skin files -- files with a .wmz extension located in the Program Files\Windows Media Player\Skins folder -- are actually just renamed .zip files. This means that it is possible to inspect the implementation of any existing skin by uncompressing and looking at the files in the skin's archive.

I had previously assumed that skin files were created using some difficult-to-learn proprietary language, but as I found by looking at existing skins on my system, I found that the layout and behavior of WMP skins are defined in standard XML and Javascript code. Once I discovered that, I set out to create my own skin that would have the design and behavior that I wanted.

I was able to implement the new skin by learning by example from the skins already on my system. Between inspection of the source code of the Microsoft skin "9SeriesDefault" (internally named "Corona") and a skin I had previously downloaded in my earlier search for a suitable skin named Basic6, I was able to write 95% of the implementation for my new skin; the remainder I was able to get from Microsoft's skinning reference at MSDN.

The UI layout of the new skin was inspired by the TrackInfo pane of the Basic 6 skin, written by Richard Kohut, which featured display of all of the information I wanted (album, artist, track name), plus display of the album art for the album, a cool touch. For the play controls of the UI, I created a reduced-size version of the standard Windows Media Player 11 interface -- thus the name for the new skin, "Schneider's Eleven".

To my surprise, the technique for displaying the album art in the skin doesn't appear to be documented anywhere in the MSDN online documentation -- or anywhere else on the WWW that I could find! The key is setting the background image property of a subview element in the skin to the constant string value "WMPImage_AlbumArtSmall". At the time of this writing there is only a single result returned in a Google search for WMPImage_AlbumArtSmall (and that result is a long discussion thread in Japanese which I can't read)! As I mentioned, I find this really surprising -- is there another way to put album art into a skin; or else maybe the technique for putting album art in a WMP skin is still little-known, or maybe there just isn't a lot of discussion of WMP skinning out there? I'm not sure at this point. In any event, if you are a skinner looking to put album art into your skin, by all means download a copy of Schneider's Eleven and take a look at the source to see how I did it.

Doing the same search in Google News currently returns 4 results, one of which is a post to microsoft.public.windowsmedia.player.skins by "Stevie BM" describing a technique to work around an issue with album art not being sized properly when displayed in a skin, which I took advantage of in my skin. Thanks Stevie! It's always very cool when someone, having found a solution to a tricky problem, takes the time to follow up and let the community know about the solution, rather than just silently implementing the solution without letting anyone else know about it.

If you'd like to download a copy of the Schneider's Eleven skin, follow that link to my web site for the download link. Feedback on the skin would be welcome!