Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0

Mapping documents:

Superclass:

<?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="accountStore.sor.ScheduleOfRates"
table="SCHEDULE_OF_RATES">

<id name="id">
<generator class="increment"/>
</id>

<property name="code"/>
<property name="description"/>
<property name="rate" />
<property name="unit"/>
<property name="priority"/>
<property name="detail"/>
<!--property name="category" /-->
</class>

</hibernate-mapping>

Subclass:

<?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>
<joined-subclass name="accountStore.worksorder.ScheduleOfRatesWO"
table="SCHEDULE_OF_RATES_WO"
extends="accountStore.sor.ScheduleOfRates">

<key column="SOR_ID"/>
<property name="worksOrderId"/>
<property name="quantity"/>
<property name="total"/>
</joined-subclass>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

String hql = "from ScheduleOfRates scheduleOfRates where scheduleOfRates.code = '601101'";

Query q = session.createQuery(hql);
ScheduleOfRates sor = (ScheduleOfRates) q.uniqueResult();
System.out.println("code:"+ sor.getCode());

Full stack trace of any exception that occurs:

Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:603)
at test.HibTest2.main(HibTest2.java:29)
Caused by: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'case when scheduleof0_1_.SOR_ID is not null then 1 when scheduleof0_.id is not null then 0 end'.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
at sun.jdbc.odbc.JdbcOdbc.SQLPrepare(JdbcOdbc.java:4831)
at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(JdbcOdbcConnection.java:475)
at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(JdbcOdbcConnection.java:443)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:396)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:334)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:88)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1162)
at org.hibernate.loader.Loader.doQuery(Loader.java:390)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 7 more

Name and version of the database you are using:

MS ACCESS 2002 (XP)

The generated SQL (show_sql=true):

Hibernate: select scheduleof0_.id as id, scheduleof0_.code as code3_, scheduleof0_.description as descript3_3_, scheduleof0_.rate as rate3_, scheduleof0_.unit as unit3_, scheduleof0_.priority as priority3_, scheduleof0_.detail as detail3_, scheduleof0_1_.worksOrderId as worksOrd2_4_, scheduleof0_1_.quantity as quantity4_, scheduleof0_1_.total as total4_, case when scheduleof0_1_.SOR_ID is not null then 1 when scheduleof0_.id is not null then 0 end as clazz_ from SCHEDULE_OF_RATES scheduleof0_ left outer join SCHEDULE_OF_RATES_WO scheduleof0_1_ on scheduleof0_.id=scheduleof0_1_.SOR_ID where scheduleof0_.code='601101'


_________________________________________
Reading the subclass bean from the database works fine.
But when trying to read a superclass from the database the generated SQL has syntax errors in the 'case when' statement.

Why does reading a superclass entity create a join with the subclass in the first place?

I know nothing about MS Access but it looks like you don't have specified hibernate sql dialect, so hibernate generates bad sql case when expression,

Thanks for your advice.
I am using the GenericDialect.java. So i most likely need a specific MSAccess dialect.
Searching the net, the only one written seems to be http://www.tobychampion.co.uk/Access2000Dialect.java

but it appears the fees were not paid and the domain is lost.

Does anyone have an MSAccess SQL Dialect class? ideally for Hibernate 3, MS Access 2002.

Thanks
Darren

The Access2000dialect did not resolve this. Any other ideas?

use a database that supports proper sql ;)