Hi, I am a newbie to hibernate and I need to use createSQLQuery because my oracle query has many date functions and CASE WHEN statements.
but even my SQL is not working.

Hibernate version:3.0
Database - Oralce 10g

Mapping document contains -
<hibernate-mapping>

<class name="test.Summary" table="summary">
<id unsaved-value="null" name="SummaryId" column="SUMMARY_ID">
<generator class="uuid.hex"/>
</id>
<property name="Summarydt" column="SUMMARY_date" />
<property name="Summarycharges" column="Summary_charges" />

</class>
</hibernate-mapping>

String queryString =
SELECT to_char(summary.summary_date,'YYYY') as Summarydt, summary_id as SummaryId FROM summary summary WHERE summary.summary_date between add_months(trunc(sysdate,'Q'),-24) and add_months(sysdate,-3)

test.summary class has getter - setter for summaryId and summaryDt.

SQLQuery query = session.createSQLQuery(queryString.toString()).addScalar( "SummaryDt", Hibernate.STRING).addScalar( "SummaryId", Hibernate.STRING).addEntity("sum", test.Summary.class);

Can you please tell me, what am I missing here? Can hibernate3.0 work for such query with to_char etc ?

srtacktrace -

exception is org.hibernate.exception.SQLGrammarException: could not execute query
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:1502)
at org.hibernate.loader.Loader.list(Loader.java:1482)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:103)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1333)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:146)
at Test3.main(Test3.java:146)
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:5971)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1527)
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:1482)
at org.hibernate.type.StringType.get(StringType.java:16)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
at org.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:650)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:277)
at org.hibernate.loader.Loader.doQuery(Loader.java:384)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:203)
at org.hibernate.loader.Loader.doList(Loader.java:1499)
... 5 more



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

Hibernate version:

Mapping documents:

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

Full stack trace of any exception that occurs:

Name and version of the database you are using:

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html