I have the following query that retrieves childscorecards from the parent based on effective date in a many to many relationship:
select scorecard from Scorecard scorecard join scorecard.parents as parent where parent.id.id = :scorecardId and :date between scorecard.effectiveDates.startDate and scorecard.effectiveDates.endDate order by scorecard.order

When I run this in the hibernate console it works as expected but when I run it in my code I get the following:

*** WARNING: Keyword 'order' is being intepreted as an identifier due to: expecting IDENT, found 'order'

any ideas? Thanks in advance for your help!

Hibernate version: 3.05

Mapping documents:
scorecard.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="net.hcsc.service.perfmngmt.scorecard.Scorecard" table="SCORECARD">
<composite-id name="id" class="net.hcsc.service.perfmngmt.scorecard.ScorecardId" unsaved-value="undefined">
<key-property name="id" column="SCORECARD_ID" type="string" />
<key-property name="version" column="SCORECARD_VERSION" type="integer" />
</composite-id>
<timestamp name="updateDateTime" column="SCORECARD_LAST_UPDATE" unsaved-value="null" />
<component name="name" class="net.hcsc.service.perfmngmt.scorecard.ScorecardName">
<property name="name" column="SCORECARD_NAME" type="string" />
</component>
<property name="description" column="SCORECARD_DESCRIPTION" type="string" not-null="false" length="5000" />
<property name="order" column="SCORECARD_ORDER" type="integer" not-null="false" length="5000" />
<!--<component name="comment" class="net.hcsc.service.perfmngmt.result.Comment">
<property name="title" column="SCORECARD_COMMENT_TITLE" type="string" not-null="false"/>
<property name="comment" column="SCORECARD_COMMENT" type="net.hcsc.service.perfmngmt.persistance.TextType" not-null="false" length="700"/>
</component>-->
<component name="effectiveDates" class="net.hcsc.util.id.EffectiveDates">
<property name="startDate" column="SCORECARD_START_DATE" type="calendar" not-null="false" />
<property name="endDate" column="SCORECARD_END_DATE" type="calendar" not-null="false" />
</component>
<!-- Parent can be null for root categories. -->

<map name="indicators" table="INDICATORS" cascade="all">
<key>
<column name="SCORECARD_ID" />
<column name="SCORECARD_VERSION" />
</key>
<map-key column="INDICATOR" type="string" />
<!--<map-key formula="indicator" type="string"/>-->
<!--<many-to-many column="INDICATOR_ID" unique="true" class="net.hcsc.service.perfmngmt.indicator.ScorecardIndicator"/>-->
<one-to-many class="net.hcsc.service.perfmngmt.indicator.ScorecardIndicator" />
</map>
<set name="childScorecards" cascade="all" table="SCORECARD_CHILDREN" inverse="false" lazy="false">
<key>
<column name="PARENT_SCORECARD_ID" />
<column name="PARENT_SCORECARD_VERSION" />
</key>
<!--<composite-map-key class="net.hcsc.service.perfmngmt.scorecard.ScorecardId">
<key-property name="id" column="SCORECARD_ID" type="string"/>
<key-property name="version" column="SCORECARD_VERSION" type="integer"/>
</composite-map-key>-->
<many-to-many class="net.hcsc.service.perfmngmt.scorecard.Scorecard">
<column name="CHILD_SCORECARD_ID" />
<column name="CHILD_SCORECARD_VERSION" />
</many-to-many>
</set>
<set name="parents" cascade="all" table="SCORECARD_CHILDREN" lazy="false" inverse="true">
<key>
<column name="CHILD_SCORECARD_ID"/>
<column name="CHILD_SCORECARD_VERSION"/>
</key>
<many-to-many class="net.hcsc.service.perfmngmt.scorecard.Scorecard">
<column name="PARENT_SCORECARD_ID" not-null="true" />
<column name="PARENT_SCORECARD_VERSION" not-null="true" />
</many-to-many>
</set>
<map name="measures" cascade="all" lazy="true">
<key>
<column name="SCORECARD_ID" />
<column name="SCORECARD_VERSION" />
</key>
<composite-map-key class="net.hcsc.util.id.VersionedID">
<key-property name="id" column="ASSOC_MEASURE_ID" type="string" />
<key-property name="version" column="ASSOC_MEASURE_VERSION" type="integer" />
</composite-map-key>
<one-to-many class="net.hcsc.service.perfmngmt.scorecard.AssociatedMeasure"/>
</map>
<map name="properties" table="PROPERTIES" cascade="all" lazy="true">
<key>
<column name="SCORECARD_ID" />
<column name="SCORECARD_VERSION" />
</key>
<map-key column="PROPERTY_NAME" type="string" />
<one-to-many class="net.hcsc.service.perfmngmt.properties.Property" />
</map>
<many-to-one name="comment" column="COMMENT_ID" not-null="false" class="net.hcsc.service.perfmngmt.result.Comment" cascade="all" lazy="false" />
</class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():
String query = "select scorecard from Scorecard scorecard join scorecard.parents as parent where parent.id.id = :scorecardId and :date between scorecard.effectiveDates.startDate and scorecard.effectiveDates.endDate order by scorecard.order";
Iterator childIterator;
List children;
Scorecard child;

try {
children = getSession().createQuery(query)
.setParameter("scorecardId", aScorecard.getId().getId())
.setParameter("date", date)
.list();

childIterator = children.iterator();
while (childIterator.hasNext()) {
child = (Scorecard)childIterator.next();

loadChildScorecards(child, date);
loadMeasures(child,date);

Hibernate.initialize(child.getIndicators());
Hibernate.initialize(child.getProperties());
Hibernate.initialize(child.getChildScorecards());
Hibernate.initialize(child.getMeasures());

aScorecard.addChildScorecard(child);
}
} catch (DataAccessResourceFailureException darfe) {
throw new UnableToRetrieveObjectException(new TraceableException(darfe));
} catch (HibernateException he) {
throw new UnableToRetrieveObjectException(new TraceableException(he));
} catch (IllegalStateException ise) {
throw new UnableToRetrieveObjectException(new TraceableException(ise));
}


Full stack trace of any exception that occurs:

Name and version of the database you are using:
SQL Server 2000
The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Can you add the generated SQL too. And please make use of the code tag.

here is the generated sql sorry about the code tag

I had a look in the grammar files and there's some jiggery-pokery to do with re-interpreting keywords as identifiers when necessary. It looks like that was retrofitted, so there may be a case that a developer has forgotten to take into account.

I recommend playing with your where clause: first try swapping the two parts (perhaps the parentheses added around the "between" clause is causing the confusion), then try dropping the where clause entirely. If you find that dropping the between clause works, replace it with <=/>= dates and raise a JIRA issue about it. If you have to drop the where clause entirely, I think I'd have to recommend going without the order by clause, and ordering the results in java code instead.

Sorry I can't help more. My antlr is rudimentary at best, the grammar files are confounding me.

Thanks for the help. I thought that might be the case. I checked the generated SQL from both the application and the hibernate console and they looked the same. I will try working with my query