Is it possible to do a Select Distinct on a computed column using HQL ?

I have the following perfectly valid SQL statement :

SELECT DISTINCT zp.zipCode / 100 from ZipCodes zp Where zp.referenceId = 200;

I try the same thing in as HQL :

SELECT DISTINCT zp.zipCode / 100 from ZipCodes as zp Where zp.referenceId = 200;

and I get the following error :

net.sf.hibernate.QueryException: , expected in SELECT [SELECT DISTINCT zp.zipCode/100 From ......]

If I change the query to

SELECT DISTINCT zp.zipCode from ZipCodes as zp Where zp.referenceId = 200;

It works like a charm, although the resulting Collection is not at all what I want.


We are using Hibernate 2.1.7.

work perfectly in hibernate 3

try this :

SELECT DISTINCT (zp.zipCode / 100) from ZipCodes zp Where zp.referenceId = 200;

or upgrade to hibernate 3

I haven't hiberante2 for test

I've been there already. Hibernate doesn't parse it correctly with the parens.

Migrating the application to use Hibernate 3 is not an option for the current release.

I think that hibernate developer don't work on hibernate2 and if it doesn't work then ...

I don't know if you're ready to think about workarounds yet.. but since you say you can't upgrade, once possibility might be to create an extra column in your database (or a view, if this is a read-only table) to provide you the zipcode / 100.

Anyways. Maybe I'm stating the obvious. That said, I just finished the migration to 3 from 2.1.7 and it went much smoother than I thought it would.