Hi,

The same query runs fine on SQL editor but not via hibernate. It always throws be Invalid column name on the joins. I tired using ordinary joins with just putting = but still not working. Any ideas?

Code:

Query q = sess.createSQLQuery(
"SELECT * from STRENGTH_WORKOUT {A} join STRENGTH_ACTIVITY {B} on A.Workout_nr = B.workout_nr" +
" JOIN STRENGTH_SET {C} ON B.ACTIVITY_NR=C.ACTIVITY_NR "+
" WHERE A.PERSON_NR = :personNr AND A.WORKOUT_DATE >= :date AND A.IS_DELETED = 'N' "+" AND B.EXERCISE_NR = :exerciseNr",
new String[] {"A","B","C"},
new Class[] {StrengthWorkout.class,StrengthActivity.class,StrengthSet.class});


Output from q.getQueryString()

[b]Query String is SELECT * from STRENGTH_WORKOUT {A} join STRENGTH_ACTIVITY {B} on A.Workout_nr = B.workout_nr JOIN STRENGTH_SET {C} ON B.ACTIVITY_NR=C.ACTIVITY_NR WHERE A.PERSON_NR = :personNr AND A.WORKOUT_DATE >= :date AND A.IS_DELETED = 'N' AND B.EXERCISE_NR = :exerciseNr[/b]


Output from hibernate show_sql

SELECT * from STRENGTH_WORKOUT A join STRENGTH_ACTIVITY B on A.Workout_nr = B.workout_nr JOIN STRENGTH_SET C ON B.ACTIVITY_NR=C.ACTIVITY_NR WHERE A.PERSON_NR = ? AND A.WORKOUT_DATE >= ? AND A.IS_DELETED = 'N' AND B.EXERCISE_NR
= ?

ERROR:

11:36:37,300 WARN JDBCExceptionReporter:38 - SQL Error: 17006, SQLState: null
11:36:37,300 ERROR JDBCExceptionReporter:46 - Invalid column name
11:36:37,300 WARN JDBCExceptionReporter:38 - SQL Error: 17006, SQLState: null
11:36:37,300 ERROR JDBCExceptionReporter:46 - Invalid column name
11:36:37,331 ERROR JDBCExceptionReporter:38 - SQLException occurred
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.j
ava:6221)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl
.java:1557)
at oracle.jdbc.driver.OracleResultSet.getLong(OracleResultSet.java:1540)

at com.mchange.v2.sql.filter.FilterResultSet.getLong(FilterResultSet.jav
a:379)
at net.sf.hibernate.type.LongType.get(LongType.java:18)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:352)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:203)
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.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3797)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at com.win.core.dao.strength.StrengthCalendarManager.getEvents(StrengthC
alendarManager.java:230)
at com.win.core.bl.strength.ReplaceStrength.changeStrengthPermanent(Repl
aceStrength.java:155)
at com.win.ishape.action.strength.ReplaceStrengthAction.processChangeCom
pleted(ReplaceStrengthAction.java:130)
at com.win.ishape.action.strength.ReplaceStrengthAction.execute(ReplaceS
trengthAction.java:63)
at org.apache.struts.action.RequestProcessor.processActionPerform(Reques
tProcessor.java:480)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.ja
va:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:142
0)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:520)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:165)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:103)
at com.caucho.server.dispatch.ServletFilterChain.doFilter(ServletFilterC
hain.java:113)
at com.win.ishape.action.common.RedirectFilter.doFilter(RedirectFilter.j
ava:80)
at com.caucho.server.dispatch.FilterFilterChain.doFilter(FilterFilterCha
in.java:84)
at com.caucho.server.cache.CacheFilterChain.doFilter(CacheFilterChain.ja
va:177)
at com.caucho.server.webapp.WebAppFilterChain.doFilter(WebAppFilterChain
.java:177)
at com.caucho.server.dispatch.ServletInvocation.service(ServletInvocatio
n.java:221)
at com.caucho.server.http.HttpRequest.handleRequest(HttpRequest.java:262
)
at com.caucho.server.port.TcpConnection.run(TcpConnection.java:315)
at com.caucho.util.ThreadPool.runTasks(ThreadPool.java:353)
at com.caucho.util.ThreadPool.run(ThreadPool.java:302)
at java.lang.Thread.run(Thread.java:536)
11:36:37,347 INFO HibernateSessionFactory:87 - Closing hibernate session.

Thanks
Raghu