Hello.
I have a situation where I need to use a resultant table of a subquery as a table in the join of a SQL statement. I am using Oracle 8i as the database. I was able to execute the native SQL without any errors using PL/SQL and other Oracle tools like TOAD.
However, when I convert the native SQL to HQL, I get an Query exception stating "in expected". Greatly appreciate if anyone can help in guiding me the right path to fix the problem.
The Mapping documents section shows the Hibernate mapping file and the HQL file I am using. The stack trace is also mentioned below.
Thanks in advance for help.
Hibernate version:
Hibernate 2.1.4
Mapping documents:
<hibernate-mapping>
<!--
Define the class and table mapping
-->
<class name="com.xxx.datatype.business.CityInfo" table="ADDRESS_VALIDATION">
<!--
Define the identifier of the CityInfo Object
-->
<composite-id name="key" class="com.xxx.datatype.business.CityInfo$CityInfoKey">
<key-property name="postalCode" column="AV_ZIPCODE"/>
<key-property name="city" column="AV_CITY"/>
<key-property name="county" column="AV_COUNTY"/>
<key-property name="stateOrProvince" column="AV_STATE"/>
</composite-id>
<property name="seqNo" column="AV_SEQNO"/>
</class>
</hibernate-mapping>
Named Query
<query name="City.State.by.Zip"><![CDATA[
select CityInfo from com.xxx.datatype.business.CityInfo as CityInfo,
(select min(CityInfo.seqNo), CityInfo.key.postalCode, CityInfo.key.stateOrProvince, CityInfo.key.city
from com.xxx.datatype.business.CityInfo
where CityInfo.key.postalCode = :zipcode
group by CityInfo.key.postalCode, CityInfo.key.stateOrProvince, CityInfo.key.city ) as CityInfoSub
where CityInfo.key.postalCode = CityInfoSub.postalCode
and CityInfo.key.stateOrProvince = CityInfoSub.stateOrProvince
and CityInfo.key.city = CityInfoSub.city
and CityInfo.seqNo = CityInfoSub.seqNo
]]></query>
Full stack trace of any exception that occurs:
Query Details:
com.xxx.framework.data.services.Query@7797d80d[_type=Named HQL,_session=com.xxx.framework.system.services.HibernateServiceConnection@6ea6d81b,_service=EFE Data Access Service,_query=net.sf.hibernate.impl.QueryImpl@38c6581b,_maxResults=2147483647,_hql=City.State.by.Zip,_failOnMaxRows=false]
select CityInfo from com.xxx.datatype.business.CityInfo as CityInfo,
(select min(CityInfo.seqNo) as seqNo, CityInfo.key.postalCode, CityInfo.key.stateOrProvince, CityInfo.key.city
from com.xxx.datatype.business.CityInfo
where CityInfo.key.postalCode = :zipcode
group by CityInfo.key.postalCode, CityInfo.key.stateOrProvince, CityInfo.key.city ) as CityInfoSub
where CityInfo.key.postalCode = CityInfoSub.postalCode
and CityInfo.key.stateOrProvince = CityInfoSub.stateOrProvince
and CityInfo.key.city = CityInfoSub.city
and CityInfo.seqNo = CityInfoSub.seqNo
Parameter(s): Zip Code: 97230
Exception Detials:
Message:in expected: select [
select CityInfo from com.xxx.datatype.business.CityInfo as CityInfo,
(select min(CityInfo.seqNo) as seqNo, CityInfo.key.postalCode, CityInfo.key.stateOrProvince, CityInfo.key.city
from com.xxx.datatype.business.CityInfo
where CityInfo.key.postalCode = :zipcode
group by CityInfo.key.postalCode, CityInfo.key.stateOrProvince, CityInfo.key.city ) as CityInfoSub
where CityInfo.key.postalCode = CityInfoSub.postalCode
and CityInfo.key.stateOrProvince = CityInfoSub.stateOrProvince
and CityInfo.key.city = CityInfoSub.city
and CityInfo.seqNo = CityInfoSub.seqNo
]
ToString:net.sf.hibernate.QueryException: in expected: select [
select CityInfo from com.xxx.datatype.business.CityInfo as CityInfo,
(select min(CityInfo.seqNo) as seqNo, CityInfo.key.postalCode, CityInfo.key.stateOrProvince, CityInfo.key.city
from com.xxx.datatype.business.CityInfo
where CityInfo.key.postalCode = :zipcode
group by CityInfo.key.postalCode, CityInfo.key.stateOrProvince, CityInfo.key.city ) as CityInfoSub
where CityInfo.key.postalCode = CityInfoSub.postalCode
and CityInfo.key.stateOrProvince = CityInfoSub.stateOrProvince
and CityInfo.key.city = CityInfoSub.city
and CityInfo.seqNo = CityInfoSub.seqNo
]
Stacktrace:
net.sf.hibernate.QueryException: in expected: select [
select CityInfo from com.xxx.datatype.business.CityInfo as CityInfo,
(select min(CityInfo.seqNo) as seqNo, CityInfo.key.postalCode, CityInfo.key.stateOrProvince, CityInfo.key.city
from com.xxx.datatype.business.CityInfo
where CityInfo.key.postalCode = :zipcode
group by CityInfo.key.postalCode, CityInfo.key.stateOrProvince, CityInfo.key.city ) as CityInfoSub
where CityInfo.key.postalCode = CityInfoSub.postalCode
and CityInfo.key.stateOrProvince = CityInfoSub.stateOrProvince
and CityInfo.key.city = CityInfoSub.city
and CityInfo.seqNo = CityInfoSub.seqNo
]
at net.sf.hibernate.hql.FromParser.token(FromParser.java:102)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:294)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1562)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1533)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at com.xxx.framework.data.services.Query.list(Query.java:310)
at com.xxx.transactionsvc.data.das.SystemDAS.findCityStateByZip(SystemDAS.java:63)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java(Compiled Code))
at java.lang.reflect.Method.invoke(Method.java(Compiled Code))
at org.apache.commons.beanutils.MethodUtils.invokeMethod(MethodUtils.java:252)
at org.apache.commons.beanutils.MethodUtils.invokeMethod(MethodUtils.java:198)
at com.xxx.framework.data.jdo.BasePersistable.findData(BasePersistable.java:1083)
at com.xxx.datatype.business.CityInfo.findCityStateByZip(CityInfo.java:189)
at com.xxx.transactionsvc.webservice.SystemService.findCityStateByZip(SystemService.java:34)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:79)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java(Compiled Code))
at java.lang.reflect.Method.invoke(Method.java(Compiled Code))
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:382)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:279)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:319)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:450)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:285)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:301)
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:283)
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.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:983)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:564)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:200)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:119)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:276)
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:334)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:618)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:439)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:672)
Name and version of the database you are using:
Oracle 8i
Login
FAQ
Search
