hello,
I am using oracle 8i with Jdk1.4 and OracleDialect. I need to execute a sql query where the schema says that an employeeLeave has multiple statuses. I am trying to run the following java code.

Transaction tx = sess.beginTransaction();
String[] strParams = new String[]{"a","b","c"};
Class[] clsParams = new Class[]{LoaEmployeeLeave.class,LoaEmplLeaveStatu.class, LoaEmplLeaveStatu.class };

Query q = sess.createSQLQuery("select {a.*} from loa_employee_leave {a}, " +
"loa_empl_leave_status {b} where {a}.id={b}.loa_empl_leave_id" +
" and {b}.next_ssn='"+supvrData.getSsn()+"' and {b}.DATE_CREATED = "+
"( select max({c}.DATE_CREATED) from loa_empl_leave_status {c} "+
" where {c}.LOA_EMPL_LEAVE_ID={a}.id)", strParams, clsParams);

List lt = q.list();
for(int i=0;i<lt.size();++i){
LoaEmployeeLeave emplLv = (LoaEmployeeLeave)lt.get(i);
leaves.addXMLObjectToRoot(emplLv.toXml(null));
}
tx.commit();

It generates the following query

select a.ID as ID0_, a.SSN as SSN0_, a.CREATOR_SSN as CREATOR_3_0_, a.FROM_DATE as FROM_DATE0_, a.TO_DATE as TO_DATE0_,
a.EMAIL as EMAIL0_, a.LOA_ACTION as LOA_ACTION0_, a.RETURN_TO_WORK_DT as RETURN_T8_0_, a.CREATE_DATE as CREATE_D9_0_,
a.STATUS as STATUS0_, a.LOA_TYPE_ID as LOA_TYP11_0_ from loa_employee_leave a, loa_empl_leave_status b where
a.id=b.loa_empl_leave_id and b.next_ssn='467352244' and b.DATE_CREATED = ( select max(c.DATE_CREATED) from
loa_empl_leave_status c where c.LOA_EMPL_LEAVE_ID=a.id)

This fails with the following stack trace

INFO: Sbcuid:jl6841 role:e:s
Hibernate: select a.ID as ID0_, a.SSN as SSN0_, a.CREATOR_SSN as CREATOR_3_0_, a
.FROM_DATE as FROM_DATE0_, a.TO_DATE as TO_DATE0_, a.EMAIL as EMAIL0_, a.LOA_ACT
ION as LOA_ACTION0_, a.RETURN_TO_WORK_DT as RETURN_T8_0_, a.CREATE_DATE as CREAT
E_D9_0_, a.STATUS as STATUS0_, a.LOA_TYPE_ID as LOA_TYP11_0_ from loa_employee_l
eave a, loa_empl_leave_status b where a.id=b.loa_empl_leave_id and b.next_ssn='4
67352244' and b.DATE_CREATED = ( select max(c.DATE_CREATED) from loa_empl_leave_
status c where c.LOA_EMPL_LEAVE_ID=a.id)
Apr 16, 2004 3:48:15 AM net.sf.hibernate.util.JDBCExceptionReporter logException
s
WARNING: SQL Error: 17006, SQLState: null
Apr 16, 2004 3:48:15 AM net.sf.hibernate.util.JDBCExceptionReporter logException
s
SEVERE: Invalid column name
Apr 16, 2004 3:48:15 AM net.sf.hibernate.util.JDBCExceptionReporter logException
s
WARNING: SQL Error: 17006, SQLState: null
Apr 16, 2004 3:48:15 AM net.sf.hibernate.util.JDBCExceptionReporter logException
s
SEVERE: Invalid column name
Apr 16, 2004 3:48:15 AM net.sf.hibernate.JDBCException <init>
SEVERE: SQLException occurred
java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.j
ava)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl
.java)
at oracle.jdbc.driver.OracleResultSet.getTimestamp(OracleResultSet.java)

at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingRe
sultSet.java:229)
at net.sf.hibernate.type.TimestampType.get(TimestampType.java:22)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.type.AbstractType.hydrate(AbstractType.java:66)
at net.sf.hibernate.type.ComponentType.hydrate(ComponentType.java:371)
at net.sf.hibernate.type.ComponentType.nullSafeGet(ComponentType.java:14
5)
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:950)
at net.sf.hibernate.loader.Loader.list(Loader.java:941)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3764)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at com.sbc.hrtech.loa.server.businesslogic.utility.LOAUtility.getEmploye
eLeavesForSupervisor(LOAUtility.java:306)
at com.sbc.hrtech.loa.ui.action.ReviewLOAAction.execute(ReviewLOAAction.
java:38)
at org.apache.struts.action.RequestProcessor.processActionPerform(Reques
tProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.ja
va:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:148
2)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:284)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:204)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDisp
atcher.java:742)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(Applica
tionDispatcher.java:506)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationD
ispatcher.java:443)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDis
patcher.java:359)
at org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.
java:1069)
at org.apache.struts.action.RequestProcessor.processForwardConfig(Reques
tProcessor.java:455)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.ja
va:279)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:148
2)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:284)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:204)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
alve.java:257)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:564)
at org.apache.catalina.core.StandardContextValve.invokeInternal(Standard
ContextValve.java:245)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
alve.java:199)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:564)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
ava:195)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:151)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
ava:164)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:149)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:564)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
ve.java:156)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValv
eContext.java:151)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.jav
a:564)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:972)

at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:20
6)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java
:828)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.proce
ssConnection(Http11Protocol.java:700)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java
:584)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadP
ool.java:683)
at java.lang.Thread.run(Thread.java:534)

If I run this query directly on oracle it works...
Any suggesstions?

Thanks,
Sohil

have you tried without the subselect? just to see if the problem really comes from subselect?

Tried it without the subquery but same result...The generated query works in Oracle directly. Am totally clueless now. This is a pretty simple join at this point.

Hibernate: select a.ID as ID0_, a.SSN as SSN0_, a.CREATOR_SSN as CREATOR_3_0_, a.FROM_DATE as FROM_DATE0_, a.TO_DATE as TO_DATE0_, a.EMAIL as EMAIL0_, a.LOA_ACTION as LOA_ACTION0_, a.RETURN_TO_WORK_DT as RETURN_T8_0_, a.CREATE_DATE as CREATE_D9_0_, a.STATUS as STATUS0_, a.LOA_TYPE_ID as LOA_TYP11_0_ from loa_employee_leave a, loa_empl_leave_status b where a.id=b.loa_empl_leave_id and b.next_ssn='467352244'

Apr 16, 2004 11:24:37 AM net.sf.hibernate.util.JDBCExceptionReporter
java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.j
ava)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl
.java)
at oracle.jdbc.driver.OracleResultSet.getTimestamp(OracleResultSet.java)

at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingRe
sultSet.java:229)
at net.sf.hibernate.type.TimestampType.get(TimestampType.java:22)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.type.AbstractType.hydrate(AbstractType.java:66)
at net.sf.hibernate.type.ComponentType.hydrate(ComponentType.java:371)
at net.sf.hibernate.type.ComponentType.nullSafeGet(ComponentType.java:14
5)
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:950)
at net.sf.hibernate.loader.Loader.list(Loader.java:941)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3764)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at com.sbc.hrtech.loa.server.businesslogic.utility.LOAUtility.getEmploye
eLeavesForSupervisor(LOAUtility.java:311)
at com.sbc.hrtech.loa.ui.action.ReviewLOAAction.execute(ReviewLOAAction.
java:38)
at org.apache.struts.action.RequestProcessor.processActionPerform(Reques
tProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.ja
va:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:148
2)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:284)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:204)
at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDisp
atcher.java:742)
at org.apache.catalina.core.ApplicationDispatcher.processRequest(Applica
tionDispatcher.java:506)
at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationD
ispatcher.java:443)
at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDis
patcher.java:359)
at org.apache.struts.action.RequestProcessor.doForward(RequestProcessor.
java:1069)
at org.apache.struts.action.RequestProcessor.processForwardConfig(Reques
tProcessor.java:455)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.ja
va:279)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:148
2)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:743)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:856)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:284)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:204)

Hey,
Just noticed this, what does an index have to do with this?

[b]Caused by: java.sql.SQLException: Invalid column name[/b] at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java)
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.j
ava)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl
.java)
at oracle.jdbc.driver.OracleResultSet.getTimestamp(OracleResultSet.java)

at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingRe
sultSet.java:229)
at net.sf.hibernate.type.TimestampType.get(TimestampType.java:22)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.type.AbstractType.hydrate(AbstractType.java:66)
at net.sf.hibernate.type.ComponentType.hydrate(ComponentType.java:371)
at net.sf.hibernate.type.ComponentType.nullSafeGet(ComponentType.java:14
5)
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:950)
at net.sf.hibernate.loader.Loader.list(Loader.java:941)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3764)
... 46 more

Looks like one of the properties of your object is missing from the SQL SELECT that you wrote. Since you did not bother to provide your mapping, noone can help you.

do you have a property named 'id' ?

Yes, I do have a property named id. I wasnt able to figure out why it did no work. I thought might be a driver issue but that too wasnt the case. I then mucked around with it long enough to create a hibernate query and that worked. However once I am done with the application, I will have to sit and tune hibernate cos right now its damn slow. Obviously its due to my lack of knowledge but hopefully will be able to make it faster.

Hello,
I got done with my application and found significant performance improvement by just using lazy="true". I got enormous performance gains for e.g. page loads went from 30+ seconds to 1-4 seconds. I have a very strongly defined foriegn key constraints and as I was using lazy="false" for all mappings, it was loading the entire tree for every small query I performed. Just updating note so that someone else facing same problems can derive some benefit.
Thanks,
Sohil