Hi there!

I'm trying to run the following query:

select distinct technician from Technician as technician
join technician.workingDays workingDays
where workingDays.salesorganisation = :so
order by technician.lastName, technician.technicianNo

If i call the query.iterate i get the following JDBC exception:

net.sf.hibernate.JDBCException: Could not execute query: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.


I tried to get around it by changing the query to this:

select distinct technician.lastName, technician.technicianNo from Technician as technician
...

But then i get an object array with lastName and technicianNo instead of a the technicians itself.

Any idea what i could do about it?

Aren't you using .iterate() which does not select anything except object ID?

If no, what SQL is generated from your query? (check the log)
Are lastName and technicianNo columns there?

thx mate, you just saved me 2 hours for coding a workaround which converts the object array back to a list with just the technicians.

It was the .iterate(). I'm using .list() now and it works fine.


Gavin, is this a bug or is hibernate supposed to behave like that?

http://www.hibernate.org/hib_docs/refer ... ng-data-s3

If you expect your query to return a very large number of objects, but you don't expect to use them all, you might get better performance from the iterate() methods, which return a java.util.Iterator. The iterator will load objects on demand, using the identifiers returned by an initial SQL query.

Oh its expected, I guess.

well i was using the iterate method because it's the only one that does utilize cache afaik.

well i suppose it doesn't make a a whole lot of a difference in performance, but it still would have been nice to use the cache since our technicians hardly ever change.