I'm using Hibernate 2.1.2 with Oracle 8.1.7. I'm having a issue with using the distinct keyword along with order by. I want to select only distinct Event objects because they can have multiple types and would be listed twice in the query. The query works fine as long as I do not use the order by at the end. Here's my HQL:

17:14:49,270 DEBUG QueryTranslator:201 - HQL: select distinct event from ai.calendar.model.Event as event join event.types as type where event.active = 1 and event.calendar.id = 94 and event.approved = 1 and event.detail.startDate >= TO_DATE('05-18-2004', 'MM/DD/YYYY') order by event.detail.startDate desc
17:14:49,276 DEBUG QueryTranslator:202 - SQL: select distinct event0_.EVENT_ID as EVENT_ID, event0_.BANNER_FLAG as BANNER_F2_, event0_.CREATED_DATE as CREATED_3_, event0_.DETAIL_ID as DETAIL_ID, event0_.UPDATED_DATE as UPDATED_5_, event0_.ACTIVE as ACTIVE, event0_.APPROVED as APPROVED, event0_.USER_ID as USER_ID, event0_.CALENDAR_ID as CALENDAR9_ from EVENT event0_, TYPE_ENTRY types1_, EVENT_TYPE type2_, EVENT_DETAIL detail3_ where event0_.EVENT_ID=types1_.EVENT_ID and types1_.TYPE_ID=type2_.id and event0_.DETAIL_ID=detail3_.DETAIL_ID and ((event0_.ACTIVE=1 )and(event0_.CALENDAR_ID=94 )and(event0_.APPROVED=1 )and(detail3_.START_DATE>=TO_DATE('05-18-2004' , 'MM/DD/YYYY') and event0_.DETAIL_ID=detail3_.DETAIL_ID)) order by detail3_.START_DATE desc

When Hibernate renders the query, Oracle is unhappy:

17:14:49,289 DEBUG BatcherImpl:226 - prepared statement get: select * from ( select distinct event0_.EVENT_ID as EVENT_ID, event0_.BANNER_FLAG as BANNER_F2_, event0_.CREATED_DATE as CREATED_3_, event0_.DETAIL_ID as DETAIL_ID, event0_.UPDATED_DATE as UPDATED_5_, event0_.ACTIVE as ACTIVE, event0_.APPROVED as APPROVED, event0_.USER_ID as USER_ID, event0_.CALENDAR_ID as CALENDAR9_ from EVENT event0_,TYPE_ENTRY types1_, EVENT_TYPE type2_, EVENT_DETAIL detail3_ where event0_.EVENT_ID=types1_.EVENT_ID and types1_.TYPE_ID=type2_.id and event0_.DETAIL_ID=detail3_.DETAIL_ID and ((event0_.ACTIVE=1 )and(event0_.CALENDAR_ID=94 )and(event0_.APPROVED=1 )and(detail3_.START_DATE>=TO_DATE('05-18-2004' , 'MM/DD/YYYY') and event0_.DETAIL_ID=detail3_.DETAIL_ID)) order by detail3_.START_DATE desc ) where rownum <= ?

17:14:50,284 DEBUG JDBCExceptionReporter:36 - SQL Exception
java.sql.SQLException: ORA-01791: not a SELECTed expression
(that's all that is really useful from the stack trace)

I can easily fix the query in SQL if I remove the order by from the subselect and place in before the rownum clause. Unfortunately, I cannot figure out how to get HQL to render the query this way. Any help would me appreciated. Thanks.

<hibernate-mapping>
<class
name="ai.calendar.model.Event"
table="EVENT"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="id"
column="EVENT_ID"
type="java.lang.Integer"
>
<generator class="native">
</generator>
</id>

<property
name="bannerFlag"
type="boolean"
update="true"
insert="true"
column="BANNER_FLAG"
/>

<property
name="createdDate"
type="java.util.Date"
update="true"
insert="true"
column="CREATED_DATE"
not-null="true"
/>

<many-to-one
name="detail"
class="ai.calendar.model.Detail"
cascade="all"
outer-join="auto"
update="true"
insert="true"
column="DETAIL_ID"
/>

<property
name="updatedDate"
type="java.util.Date"
update="true"
insert="true"
column="UPDATED_DATE"
not-null="true"
/>

<set
name="types"
table="TYPE_ENTRY"
lazy="true"
inverse="false"
cascade="all"
sort="unsorted"
>

<key
column="EVENT_ID"
/>
<key
column="EVENT_ID"
/>

<many-to-many
class="ai.calendar.model.Type"
column="TYPE_ID"
outer-join="auto"
/>

</set>

<property
name="active"
type="boolean"
update="true"
insert="true"
column="ACTIVE"
/>

<property
name="approved"
type="boolean"
update="true"
insert="true"
column="APPROVED"
/>

<many-to-one
name="approvedId"
class="ai.calendar.model.User"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="USER_ID"
/>

<many-to-one
name="calendar"
class="ai.calendar.model.Calendar"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="CALENDAR_ID"
not-null="true"
/>
</class>
</hibernate-mapping>
<hibernate-mapping>
<class
name="ai.calendar.model.Detail"
table="EVENT_DETAIL"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="id"
column="DETAIL_ID"
type="java.lang.Integer"
>
<generator class="native">
</generator>
</id>

<property
name="description"
type="java.lang.String"
update="true"
insert="true"
column="DESCRIPTION"
length="4000"
not-null="true"
/>

<property
name="endDate"
type="java.util.Date"
update="true"
insert="true"
column="END_DATE"
/>

<many-to-one
name="address"
class="ai.calendar.model.Address"
cascade="save-update"
outer-join="auto"
update="true"
insert="true"
column="ADDRESS_ID"
not-null="true"
/>

<many-to-one
name="contact"
class="ai.calendar.model.Person"
cascade="save-update"
outer-join="auto"
update="true"
insert="true"
column="CONTACT_ID"
not-null="true"
/>

<property
name="startDate"
type="java.util.Date"
update="true"
insert="true"
column="START_DATE"
not-null="true"
/> <property
name="title"
type="java.lang.String"
update="true"
insert="true"
column="TITLE"
not-null="true"
/>

<property
name="timezone"
type="java.util.TimeZone"
update="true"
insert="true"
column="TIMEZONE"
not-null="true"
/>

<property
name="url"
type="java.lang.String"
update="true"
insert="true"
column="URL"
/>

<many-to-one
name="county"
class="ai.calendar.model.County"
cascade="none"
outer-join="auto"
update="true"
insert="true"
column="COUNTY_ID"
not-null="true"
/>

<property
name="endHour"
type="java.lang.String"
update="true"
insert="true"
column="endHour"
length="2"
not-null="true"
/>

<property
name="endMinute"
type="java.lang.String"
update="true"
insert="true"
column="endMinute"
length="2"
not-null="true"
/>

<property
name="endPeriod"
type="java.lang.String"
update="true"
insert="true"
column="endPeriod"
length="2"
not-null="true"
/>
<property
name="startHour"
type="java.lang.String"
update="true"
insert="true"
column="startHour"
length="2"
not-null="true"
/>

<property
name="startMinute"
type="java.lang.String"
update="true"
insert="true"
column="startMinute"
length="2"
not-null="true"
/>
<property
name="startPeriod"
type="java.lang.String"
update="true"
insert="true"
column="startPeriod"
length="2"
not-null="true"
/>
</class>
</hibernate-mapping>

I think this has already been discussed not to long ago, try searching the forum ...

This closest thing that I found was a discussion on not being able to use the distinct keyword to distinct your objects. The thread discussed having to distinct the objects in my code. If that is the correct answer, then I'm not sure why I can get the result I want using SQL but not HQL. In my case, I'm doing paging so this will cause me some problems because I only want to return x results.

You'll be stucked by SQL too, because of the join.
HQL has the same limitation as SQL in this case.

I believe that the issue is that Hibernate generates an alias for the column name, but does not use it for the order by clause (i beleive it only uses the column name). Apparently there is some dispute as to what the JDBC or SQL spec is for this situation -- some developers have interpreted the spec to say that you MUST use the alias name in the order by clause.

I have also seen this issue in creating a set of drivers for HP NonStop SQLMX database (Tandem SQL).

Bottom line: Hibernate and some SQL implementations disagree.

Hi, since your post is the only post that matches "hibernate and sqlmx" I have to ask you a question: have you succeded in some way to configure hibernate to work with sqlmx?

I havn't found sqlmx in dialects that hibernate supports(chapter 3.4.1. in
http://www.hibernate.org/hib_docs/v3/re ... ernatejdbc)

We have sqlmx2 in our project and are thinking of using hibernate.
thanks in advance.

Hi All
Is it possible to use Hibernate with SQLMX? if so which dialect should be specified?

Thanks in advance

Chris

Hi, Chris
I talked with Arvin Levine about this and he told me that it can be done.
However, I still havn't started to try it(so I doesn't have an exact answer for you)