Hi,

I am using hibernate version 2.1.2 with Oracle 9I.

I have problem when i try to use Query.setMaxResults function. When this function is getting executed it is throwing ambiguous exception. When i tried to run the same query in Toad i find that the reason for ambiguous exception is * in select statement. Also my JDBC Drivers supports Scrollable Results.

If i remove setMaxResults and setFirstResult functions then the code is working fine, i mean i am able to retrieve the results.

Here is the code i am using in my program.

**********************************************************
CODE
-------

Session session=factory.openSession();
Query query=session.createQuery("select A from Grant as A inner join A.scheme.provider as prd where (A.studentId = ? AND A.rvcnStartMonth is null AND ( A.grantStartMonth > ? or (A.grantStartMonth < ? and A.grantEndMonth > ?)) AND (prd.providerCat = ? or prd.providerCat = ?))
");
query.setFirstResult(0);
query.setMaxResults(120);
List results=query.list();

**********************************************************


Here i am pasting the HQL i used.

**********************************************************
HQL
-----

select A from Grant as A inner join A.scheme.provider as prd where (A.studentId = ? AND A.rvcnStartMonth is null AND ( A.grantStartMonth > ? or (A.grantStartMonth < ? and A.grantEndMonth > ?)) AND (prd.providerCat = ? or prd.providerCat = ?))

parameter values
---------------------
1) 1
2) '01 March 2004'
3) '01 March 2004'
4) '01 March 2004'
5) 'XXX'
6) 'EO'

**********************************************************

While executing this code i get the following exception

**********************************************************
EXCEPTION
-------------

[SQL][service-j2ee]-select * from ( select grant0_.GRANT_ID as GRANT_ID, grant0_.SCHEME_ID as SCHEME_ID, grant0_.STUDENT_ID as STUDENT_ID, grant0_.GRANT_START_MONTH as GRANT_ST4_, grant0_.GRANT_END_MONTH as GRANT_EN5_, grant0_.RVCN_START_MONTH as RVCN_STA6_, grant0_.REVERSAL_PROCESSED_IND as REVERSAL7_, grant0_.REVERSAL_START_MONTH as REVERSAL8_, grant0_.REVERSAL_END_MONTH as REVERSAL9_, grant0_.AUTHORITY_REF_NO as AUTHORI10_, grant0_.VERSION_NO as VERSION_NO, grant0_.LAST_UPDATED_DATE as LAST_UP12_, grant0_.LAST_UPDATED_BY as LAST_UP13_, grant0_.scheme_id as scheme_id from RM_SUB_GRANT grant0_, RM_SUB_SCHEME scheme1_, RM_SUB_PROVIDER provider2_ where grant0_.scheme_id=scheme1_.SCHEME_ID(+) and scheme1_.provider_id=provider2_.PROVIDER_ID(+) and (((grant0_.STUDENT_ID=? )AND(grant0_.RVCN_START_MONTH is null )AND((grant0_.GRANT_START_MONTH>? )or((grant0_.GRANT_START_MONTH<? )and(grant0_.GRANT_END_MONTH>? )))AND((provider2_.PROVIDER_CAT=? )or(provider2_.PROVIDER_CAT=? )))) ) where rownum <= ?
[16/03/04 11:37:35][DEBUG][BatcherImpl][service-j2ee]-preparing statement
[16/03/04 11:37:35][DEBUG][LongType][service-j2ee]-binding '1' to parameter: 1
[16/03/04 11:37:35][DEBUG][DateType][service-j2ee]-binding '01 March 2004' to parameter: 2
[16/03/04 11:37:35][DEBUG][DateType][service-j2ee]-binding '01 March 2004' to parameter: 3
[16/03/04 11:37:35][DEBUG][DateType][service-j2ee]-binding '01 March 2004' to parameter: 4
[16/03/04 11:37:35][DEBUG][StringType][service-j2ee]-binding 'MOE' to parameter: 5
[16/03/04 11:37:35][DEBUG][StringType][service-j2ee]-binding 'EO' to parameter: 6
[16/03/04 11:37:35][DEBUG][JDBCExceptionReporter][service-j2ee]-SQL Exception
java.sql.SQLException: ORA-00918: column ambiguously defined

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)

**********************************************************

Full Stack Trace is as follows
**********************************************************
STACK TRACE
----------------

[16/03/04 11:37:35][DEBUG][QueryTranslator][service-j2ee]-HQL: select A from sg.gov.moe.rm.subsidy.persistence.Grant as A left outer join A.scheme.provider as prd where (A.studentId = ? AND A.rvcnStartMonth is null AND ( A.grantStartMonth > ? or (A.grantStartMonth < ? and A.grantEndMonth > ?)) AND (prd.providerCat = ? or prd.providerCat = ?))
[16/03/04 11:37:35][DEBUG][QueryTranslator][service-j2ee]-SQL: select grant0_.GRANT_ID as GRANT_ID, grant0_.SCHEME_ID as SCHEME_ID, grant0_.STUDENT_ID as STUDENT_ID, grant0_.GRANT_START_MONTH as GRANT_ST4_, grant0_.GRANT_END_MONTH as GRANT_EN5_, grant0_.RVCN_START_MONTH as RVCN_STA6_, grant0_.REVERSAL_PROCESSED_IND as REVERSAL7_, grant0_.REVERSAL_START_MONTH as REVERSAL8_, grant0_.REVERSAL_END_MONTH as REVERSAL9_, grant0_.AUTHORITY_REF_NO as AUTHORI10_, grant0_.VERSION_NO as VERSION_NO, grant0_.LAST_UPDATED_DATE as LAST_UP12_, grant0_.LAST_UPDATED_BY as LAST_UP13_, grant0_.scheme_id as scheme_id from RM_SUB_GRANT grant0_, RM_SUB_SCHEME scheme1_, RM_SUB_PROVIDER provider2_ where grant0_.scheme_id=scheme1_.SCHEME_ID(+) and scheme1_.provider_id=provider2_.PROVIDER_ID(+) and (((grant0_.STUDENT_ID=? )AND(grant0_.RVCN_START_MONTH is null )AND((grant0_.GRANT_START_MONTH>? )or((grant0_.GRANT_START_MONTH<? )and(grant0_.GRANT_END_MONTH>? )))AND((provider2_.PROVIDER_CAT=? )or(provider2_.PROVIDER_CAT=? ))))
[16/03/04 11:37:35][DEBUG][BatcherImpl][service-j2ee]-about to open: 0 open PreparedStatements, 0 open ResultSets
[16/03/04 11:37:35][DEBUG][SQL][service-j2ee]-select * from ( select grant0_.GRANT_ID as GRANT_ID, grant0_.SCHEME_ID as SCHEME_ID, grant0_.STUDENT_ID as STUDENT_ID, grant0_.GRANT_START_MONTH as GRANT_ST4_, grant0_.GRANT_END_MONTH as GRANT_EN5_, grant0_.RVCN_START_MONTH as RVCN_STA6_, grant0_.REVERSAL_PROCESSED_IND as REVERSAL7_, grant0_.REVERSAL_START_MONTH as REVERSAL8_, grant0_.REVERSAL_END_MONTH as REVERSAL9_, grant0_.AUTHORITY_REF_NO as AUTHORI10_, grant0_.VERSION_NO as VERSION_NO, grant0_.LAST_UPDATED_DATE as LAST_UP12_, grant0_.LAST_UPDATED_BY as LAST_UP13_, grant0_.scheme_id as scheme_id from RM_SUB_GRANT grant0_, RM_SUB_SCHEME scheme1_, RM_SUB_PROVIDER provider2_ where grant0_.scheme_id=scheme1_.SCHEME_ID(+) and scheme1_.provider_id=provider2_.PROVIDER_ID(+) and (((grant0_.STUDENT_ID=? )AND(grant0_.RVCN_START_MONTH is null )AND((grant0_.GRANT_START_MONTH>? )or((grant0_.GRANT_START_MONTH<? )and(grant0_.GRANT_END_MONTH>? )))AND((provider2_.PROVIDER_CAT=? )or(provider2_.PROVIDER_CAT=? )))) ) where rownum <= ?
[16/03/04 11:37:35][DEBUG][BatcherImpl][service-j2ee]-preparing statement
[16/03/04 11:37:35][DEBUG][LongType][service-j2ee]-binding '1' to parameter: 1
[16/03/04 11:37:35][DEBUG][DateType][service-j2ee]-binding '01 March 2004' to parameter: 2
[16/03/04 11:37:35][DEBUG][DateType][service-j2ee]-binding '01 March 2004' to parameter: 3
[16/03/04 11:37:35][DEBUG][DateType][service-j2ee]-binding '01 March 2004' to parameter: 4
[16/03/04 11:37:35][DEBUG][StringType][service-j2ee]-binding 'MOE' to parameter: 5
[16/03/04 11:37:35][DEBUG][StringType][service-j2ee]-binding 'EO' to parameter: 6
[16/03/04 11:37:35][DEBUG][JDBCExceptionReporter][service-j2ee]-SQL Exception
java.sql.SQLException: ORA-00918: column ambiguously defined

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1451)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:651)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2117)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2331)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:422)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:366)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:794)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:188)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:132)
at net.sf.hibernate.loader.Loader.doList(Loader.java:949)
at net.sf.hibernate.loader.Loader.list(Loader.java:940)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:833)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1475)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at com.hp.eframe.dataengine.impl.SessionImpl.retrieve(SessionImpl.java:344)
at com.hp.eframe.dataengine.impl.SessionImpl.retrieve(SessionImpl.java:317)
at sg.gov.moe.rm.subsidy.command.GetStudentGrantListCommand.execute(GetStudentGrantListCommand.java:95)
at com.hp.eframe.command.CommandServiceEJB.execute(CommandServiceEJB.java:67)
at com.hp.eframe.command.CommandServiceEJB_EJBObjectImpl.execute(CommandServiceEJB_EJBObjectImpl.java:23)
at com.hp.eframe.command._CommandService_Stub.execute(Unknown Source)
at com.hp.eframe.command.CommandSD.execute(CommandSD.java:66)
at sg.gov.moe.rm.common.delegate.IBENSServiceDelegate.doCommand(IBENSServiceDelegate.java:44)
at sg.gov.moe.rm.subsidy.delegate.RevokeSubsidySD.getSubsidyGrantList(RevokeSubsidySD.java:55)
at sg.gov.moe.rm.subsidy.web.action.RevokeSubsidyAction.loadRevokeData(RevokeSubsidyAction.java:193)
at sg.gov.moe.rm.subsidy.web.action.RevokeSubsidyAction.ibensExecute(RevokeSubsidyAction.java:52)
at sg.gov.moe.rm.common.web.action.IBENSAction.execute(IBENSAction.java:69)
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 org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:507)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.StandardWrapperValve.invokeServletService(StandardWrapperValve.java:720)
at org.apache.catalina.core.StandardWrapperValve.access$000(StandardWrapperValve.java:118)
at org.apache.catalina.core.StandardWrapperValve$1.run(StandardWrapperValve.java:278)
at java.security.AccessController.doPrivileged(Native Method)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:274)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:505)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:212)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:505)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:203)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:505)
at com.iplanet.ias.web.connector.nsapi.NSAPIProcessor.process(NSAPIProcessor.java:157)
at com.iplanet.ias.web.WebContainer.service(WebContainer.java:598)
**********************************************************


Can you please help me in identifying the problem in the code and where i am going wrong.

thanks.

Lokesh.

Are you sure you don't have the column RM_SUB_GRANT.SCHEME_ID mapped twice?

Can you post the relevant mappings?

Hi Steve,

Here i am providing the mapping files for the two classes.

*************************************************************
Mapping For ReqGrant
------------------------

<class name="sg.gov.moe.rm.subsidy.persistence.ReqGrant" table="RM_SUB_REQ_GRANT">
<composite-id>
<key-property column="GRANT_ID" length="14" name="grantId" type="long"/>
<key-property column="REQUEST_ID" length="14" name="requestId" type="long"/>
</composite-id>
<version column="VERSION_NO" name="versionNo" type="short"/>
<property column="STUDENT_ID" length="14" name="studentId" not-null="true" type="long"/>
<property column="GRANT_START_MONTH" length="7" name="grantStartMonth" not-null="true" type="timestamp"/>
<property column="GRANT_END_MONTH" length="7" name="grantEndMonth" not-null="true" type="timestamp"/>
<property column="RVCN_START_MONTH" length="7" name="rvcnStartMonth" type="timestamp"/>
<property column="AUTHORITY_REF_NO" length="14" name="authorityRefNo" type="long"/>
<property column="SCHEME_ID" length="14" name="schemeId" not-null="true" type="long"/>
<set name="reqGrantRates" cascade="all" lazy="false" inverse="true">
<key>
<column length="14" name="request_id"/>
<column length="14" name="grant_id"/>
</key>
<one-to-many class="ReqGrantRate"/>
</set>
</class>
*************************************************************
Mapping for ReqGrantRate
-----------------------------

<class name="ReqGrantRate" table="RM_SUB_REQ_GRANT_RATE">
<composite-id unsaved-value="any">
<key-property column="FEE_COMPONENT_ID" length="14" name="feeComponentId" type="long"/>
<key-property column="GRANT_ID" length="14" name="grantId" type="long"/>
<key-property column="REQUEST_ID" length="14" name="requestId" type="long"/>
</composite-id>
<version column="VERSION_NO" name="versionNo" type="short"/>
<property column="RATE_AMOUNT_TYPE" length="10" name="rateAmountType" type="string"/>
<property column="RATE_VALUE" length="6" name="rateValue" type="float"/>
</class>

************************************************************

Thanks for your help steve.

Regards.

Lokesh.

Steve can you pl help me in finding the error plz.......

Thanks.

Regards.

Lokesh.

Hi Steve,

Since i am new to hibernate i was confused and could not understand your answer in previous posting. After going through your answer once again i am able to solve my problem.

Also i am sorry for pasting the wrong mapping files, this happened unexpectedly. You are right that i was mapping scheme_id twice in Grant table.

Thanks once again for your help.

Regards.

Lokesh.