Hi,

I have a class mapped to a table. To specify columns for the properties, i am using alias names that i ll be giving in query.
I am getting the following SQL exception when i am trying to execute. Can somebody please help me out. Pleaaaaaseeeeee.....


Hibernate version:2.1.7

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping package="com.xxxx.common.entity">
<class name="IdDescriptionPair" table="T_PH_ORG_LOOKUP">
<id name="id" type="string" column="ID">
<generator class="assigned"/>
</id>
<property name="description" type="string" column="VALUE"></property>

</class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():
String str = "SELECT DISTINCT ORG_UNIT_LVL2_ID as ID, ORG_UNIT_LVL2_NME as VALUE FROM T_PH_ORG_LOOKUP WHERE UPPER(ORG_UNIT_LVL2_NME) LIKE 'CREDIT%'";

List li = session.createSQLQuery(str,"",IdDescriptionPair.class).list();
resultList = ( IdDescriptionPair[])li.toArray(new
IdDescriptionPair[li.size()]);


Full stack trace of any exception that occurs:
2005-02-17 09:58:29,866 [Servlet.Engine.Transports : 1] DEBUG net.sf.hibernate.impl.SessionImpl - opened session
2005-02-17 09:58:29,929 [Servlet.Engine.Transports : 1] DEBUG net.sf.hibernate.impl.SessionImpl - SQL query: SELECT DISTINCT ORG_UNIT_LVL2_ID as ID, ORG_UNIT_LVL2_NME as VALUE FROM T_PH_ORG_LOOKUP WHERE UPPER(ORG_UNIT_LVL2_NME) LIKE 'CREDIT%'
2005-02-17 09:58:29,944 [Servlet.Engine.Transports : 1] DEBUG net.sf.hibernate.impl.SessionImpl - flushing session
2005-02-17 09:58:29,944 [Servlet.Engine.Transports : 1] DEBUG net.sf.hibernate.impl.SessionImpl - Flushing entities and processing referenced collections
2005-02-17 09:58:29,944 [Servlet.Engine.Transports : 1] DEBUG net.sf.hibernate.impl.SessionImpl - Processing unreferenced collections
2005-02-17 09:58:29,944 [Servlet.Engine.Transports : 1] DEBUG net.sf.hibernate.impl.SessionImpl - Scheduling collection removes/(re)creates/updates
2005-02-17 09:58:29,944 [Servlet.Engine.Transports : 1] DEBUG net.sf.hibernate.impl.SessionImpl - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
2005-02-17 09:58:29,944 [Servlet.Engine.Transports : 1] DEBUG net.sf.hibernate.impl.SessionImpl - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
2005-02-17 09:58:29,944 [Servlet.Engine.Transports : 1] DEBUG net.sf.hibernate.impl.SessionImpl - Dont need to execute flush
2005-02-17 09:58:29,944 [Servlet.Engine.Transports : 1] DEBUG net.sf.hibernate.impl.BatcherImpl - about to open: 0 open PreparedStatements, 0 open ResultSets
2005-02-17 09:58:29,944 [Servlet.Engine.Transports : 1] DEBUG net.sf.hibernate.SQL - SELECT DISTINCT ORG_UNIT_LVL2_ID as ID, ORG_UNIT_LVL2_NME as VALUE FROM T_PH_ORG_LOOKUP WHERE UPPER(ORG_UNIT_LVL2_NME) LIKE 'CREDIT%'
2005-02-17 09:58:29,944 [Servlet.Engine.Transports : 1] DEBUG net.sf.hibernate.impl.BatcherImpl - preparing statement
2005-02-17 09:58:29,960 [Servlet.Engine.Transports : 1] DEBUG net.sf.hibernate.loader.Loader - processing result set
2005-02-17 09:58:29,991 [Servlet.Engine.Transports : 1] DEBUG net.sf.hibernate.util.JDBCExceptionReporter - SQL Exception
java.sql.SQLException: Invalid column name
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java(Inlined Compiled Code))
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java(Inlined Compiled Code))
at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java(Compiled Code))
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java(Compiled Code))
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java(Compiled Code))
at com.ibm.ws.rsadapter.jdbc.WSJdbcResultSet.getString(WSJdbcResultSet.java(Compiled Code))
at net.sf.hibernate.type.StringType.get(StringType.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:431)
at net.sf.hibernate.loader.Loader.getRowFromResultSet(Loader.java:205)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:285)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
at net.sf.hibernate.loader.Loader.list(Loader.java:1054)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3867)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at com.jpmorganchase.phoenix.service.selfassessment.controlselfassessment.dao.SelfAssessmentDAO.selectFilterValues(SelfAssessmentDAO.java:4761)
at com.jpmorganchase.phoenix.service.selfassessment.controlselfassessment.SelfAssessmentBean.searchFilterValues(SelfAssessmentBean.java:2820)
at com.jpmorganchase.phoenix.service.selfassessment.controlselfassessment.interfaces.EJSRemoteStatelesscontrolselfassessment_c3569104.searchFilterValues(Unknown Source)
at com.jpmorganchase.phoenix.service.selfassessment.controlselfassessment.interfaces._SelfAssessmentRemote_Stub.searchFilterValues(_SelfAssessmentRemote_Stub.java:1860)
at com.jpmorganchase.phoenix.common.proxy.SelfAssessmentProxy.searchFilterValues(SelfAssessmentProxy.java:577)
at com.jpmorganchase.phoenix.web.action.selfassessment.viewselfassessment.SAFilterValuesSearchAction.execute(SAFilterValuesSearchAction.java:93)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)
at com.jpmorganchase.phoenix.web.servlet.PhoenixServlet.process(PhoenixServlet.java:125)
at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:258)
at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:61)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:863)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:491)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:173)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:79)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:199)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:182)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:331)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:432)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:343)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:592)

Name and version of the database you are using:Oracle 9i

The generated SQL (show_sql=true):
SELECT DISTINCT ORG_UNIT_LVL2_ID as ID, ORG_UNIT_LVL2_NME as VALUE FROM T_PH_ORG_LOOKUP WHERE UPPER(ORG_UNIT_LVL2_NME) LIKE 'CREDIT%'

You must give Hibernate the possibility to fill in the aliases. Please have a look at the documentation for createSqlQuery.