hello
we are developing a project.this project use spring, hibernate framework and oracle database.
spring framework version is 1.1 and hibernate version is 2.0
we set oracle dialect in applicationContext.xml.
there are 3 table that are Allowance,Payment,PaymentDetail.one field that is in Allowance table,
is total of PaymentDetail's one field with a formula.

I try to set a field with using hibernate formula property but
I get a error message.otherwise query in formula space run at sqlPlus properly and
Payment't give any error message.

The query in the formula runs properly with single query like follows
"(select sum(md.miktar) from PaymentDetail md where md.PaymentDetailId=3)"

but if there is join ,query don't work:

"(select sum(md.miktar) from PaymentDetail md , Payment m
where md.Paymentid=m.Paymentid and m.FIRSTALLOWANCEID=3 )"


how can I set this derived property?


hibernate xml file:

<hibernate-mapping>
<class name="tah.AllowanceDetail" table="AllowanceDetail">
<cache usage="read-write"/>
<id name="oid" column="AllowanceDetailID" unsaved- value="0">
<generator class="increment" />
</id>

<many-to-one name="Allowance" column="AllowanceID" not-null="true"/>
<many-to-one name="arrangement" column="ARRANGEMENTNO" not-null="true"/>
<property name="amount" column="AMOUNT" not-null="true"/>
<property name="amountSpent"
formula="(select sum(md.amount) from PaymentDetail md , Payment m
where md.Paymentid=m.Paymentid and m.FIRSTALLOWANCEID=ALLOWANCEID )"
type="double"/>

</class>
</hibernate-mapping>


error message:

Could not execute query
java.sql.SQLException: ORA-00907: missing right parenthesis

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:830)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2391)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:800)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:189)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:955)
at net.sf.hibernate.loader.Loader.list(Loader.java:946)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:846)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1543)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at org.springframework.orm.hibernate.HibernateTemplate$17.doInHibernate(HibernateTemplate.java:331)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:150)
at org.springframework.orm.hibernate.HibernateTemplate.executeFind(HibernateTemplate.java:170)
at org.springframework.orm.hibernate.HibernateTemplate.find(HibernateTemplate.java:328)
at account.dao.hibernate.AccountDAOHibernate.retrieveAllowanceTertipler(AccountDAOHibernate.java:85)
at account.dao.AccountDAOTest.testRetrieveAllowanceTertipler(AccountDAOTest.java:32)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at junit.textui.TestRunner.doRun(TestRunner.java:116)
at junit.textui.TestRunner.doRun(TestRunner.java:109)
at junit.textui.TestRunner.run(TestRunner.java:72)
at junit.textui.TestRunner.run(TestRunner.java:57)

I am having the same issues. I am using Hibernate 2.12 (I know it is old). Did you find out the answer for that?