Monday, September 17, 2007

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."

No comments:

Post a Comment

Non-spammers: Thanks for visiting! Please go ahead and leave a comment; I read them all!

Attention SPAMMERS: I review all comments before they get posted, and I REPORT 100% of spam comments to Google as spam! Why not avoid getting your account banned as quickly -- and save us both a little time -- by skipping this comment form and moving on to the next one on your list? Thanks, and I hope you have a great day!