Hibernate version: 3.0

I am trying to get the following query to work:

Session session = HibernateAbstractSessionUtil.getCurrentSession();
Double query = (Double) session.createSQLQuery(
"SELECT SUM(postAmount) sumPostAmount " +
" FROM ( SELECT (CASE WHEN ENTRY_TYPE = 'DEBIT' THEN POST_AMOUNT ELSE POST_AMOUNT * -1 END) postAmount " +
" from S_GL_ENTRY_E11 ) e11")
.addScalar("sumPostAmount", Hibernate.DOUBLE)
.addScalar("postAmount", Hibernate.DOUBLE)
.uniqueResult();

sumOfPostAmounts = query.doubleValue();




The query works fine through my SQuirrel SQL client. But Hibernate is telling me about an invalid column name. Can someone suggest some ideas for me? Is this a driver issue?

The error message is as follows:

ERROR [5/03/06 13:29] HibernateAbstractDAO shaw.spectrum.core.service.ServiceErrorBean@cd8e77[User=defaultUser,Key=SP00365,Category=AUDIT,Severity=1,Description=Error reading Transaction using transaction.baseId 84.,Cause=org.hibernate.exception.SQLGrammarException: could not execute query could not execute query
[Ljava.lang.StackTraceElement;@1d4179]
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
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.custom.CustomLoader.list(CustomLoader.java:112)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
at org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:603)
at shaw.spectrum.core.transaction.persistence.TransactionDAOImpl.sumOfSimpleInterestForGivenPeriod(TransactionDAOImpl.java:540)
at shaw.spectrum.core.transaction.test.TransactionDAOTest.doTestSumOfSimpleInterestForGivenPeriod(TransactionDAOTest.java:92)
at shaw.spectrum.core.transaction.test.TransactionDAOTest.testDataBaseFunctions(TransactionDAOTest.java:80)
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 org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:478)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:344)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:6218)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1557)
at oracle.jdbc.driver.OracleResultSet.getDouble(OracleResultSet.java:1625)
at org.hibernate.type.DoubleType.get(DoubleType.java:20)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:94)
at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:133)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:329)
at org.hibernate.loader.Loader.doQuery(Loader.java:412)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 23 more

Afaik standard SQL requires "as" between a column and an alias. Perhaps your SQL client allows this but your JDBC driver doesn't? Try changing the first line to "select sum(postAmount) as sumPostAmout"

oracle doesn't mind

"SELECT SOME_VALUE someValue"

Can you turn on hibernate.show_sql=true and show us what hibernate is trying to do with your sql query?