I've been trying to understand this ANSI FROM CLAUSE for two days now, and I can't figure it out....

The Hibernate reference PDF only has a one pager on it !!!! Where can I find more information on this? I search ISO website, this forum, Sybase's website... I can't find any books. How are we suppose to learn how to use this if there's no info any where?

It's so easy with *= and =*, why is it so difficult to do with ANSI?

How can I do this...

Table1.col1 join to table2.col1 and
Table2.col2 left outer join to table3.col1 and
Table2.col3 left outer join to table4.col1


The few example I can find on the web are so simple, it's a joke... stuff like "select * from titles left join titleauthor". How's that suppose to help in the real world. :(

Thanks in advance. Sorry if I sound pissed but I am :)

Hibernate version:3.0.5

Honestly I do not understand your confusion, ANSI syntax is quite readable IMO:
Select *
FROM T1 left join T2 on T1.field1 = T2.field2

It is almost perfect English sentence: select everything from T1 and join rows from T2 where T1.field1 = T2.field2.

T1 rowset is left of T2 rowset – quite clear picture.

Thanks for your reply my friend...

I'm frustrated because I tried many,many, many variations in the FROM CLAUSE but nothing works.


Maybe you could help me with this. Here's the code and the error stack.

Thanks in advance.

CODE:

queryList = session.createQuery(
"select new ca.sshrc.web.forms.beans.programs.ParticipantDataBean(Form.formId, ApplParticipation.partId, " +
"ApplParticipation.participationType, " +
"ApplParticipation.applId, " +
"ApplParticipation.roleCode, " +
"ApplParticipation.orgDeptLocId, " +
"ApplParticipation.cid, " +
"ApplParticipation.familyName, " +
"ApplParticipation.givenName, " +
"ApplParticipation.initials, " +
"ApplParticipation.orgName, " +
"ApplParticipation.departmentName, " +
"ApplParticipation.changeDate, " +
"ApplParticipation.orgId, " +
"ApplParticipation.dataCentreId, " +
"OrgDeptLocation.departmentNameEnglish, " +
"OrgDeptLocation.departmentNameFrench, " +
"Organization.nameEnglish , " +
"Organization.nameFrench , " +
"CASE WHEN ApplParticipation.orgId=1 THEN ApplParticipation.orgName WHEN ApplParticipation.orgId<>1 and UPPER(Form.formLanguage)='F' THEN Organization.nameFrench WHEN ApplParticipation.orgId<>1 and UPPER(Form.formLanguage)='E' THEN Organization.nameEnglish ELSE '' END as COMP, " +
"Form.formLanguage) " +
"from Form as Form INNER JOIN ApplParticipation as ApplParticipation on Form.application.applId = ApplParticipation.applId, " +
"ApplParticipation LEFT OUTER JOIN OrgDeptLocation as OrgDeptLocation on ApplParticipation.orgDeptLocId = OrgDeptLocation.orgDeptLocId, "+
"ApplParticipation LEFT OUTER JOIN Organization as Organization on ApplParticipation.orgId = Organization.orgId "+
"where (Form.formId = :formId) and " +
"(ApplParticipation.roleCode in (148,149)) order by 20 ASC, 8 ASC, 9 ASC, 2 ASC").setParameter("formId", new Long(formId)).
list();




ERROR STACK:

ERROR [TP-Processor3] (ErrorCounter.java:35) - *** ERROR: line 1:1006: unexpected token: on

ERROR [TP-Processor3] (ErrorCounter.java:35) - *** ERROR: line 1:1140: unexpected token: on

ERROR [TP-Processor3] (ErrorCounter.java:35) - *** ERROR: line 1:1279: unexpected token: on

org.hibernate.hql.ast.QuerySyntaxError: unexpected token: on near line 1, column 1006 [select new ca.sshrc.web.forms.beans.programs.ParticipantDataBean(Form.formId, ApplParticipation.partId, ApplParticipation.participationType, ApplParticipation.applId, ApplParticipation.roleCode, ApplParticipation.orgDeptLocId, ApplParticipation.cid, ApplParticipation.familyName, ApplParticipation.givenName, ApplParticipation.initials, ApplParticipation.orgName, ApplParticipation.departmentName, ApplParticipation.changeDate, ApplParticipation.orgId, ApplParticipation.dataCentreId, OrgDeptLocation.departmentNameEnglish, OrgDeptLocation.departmentNameFrench, Organization.nameEnglish , Organization.nameFrench , CASE WHEN ApplParticipation.orgId=1 THEN ApplParticipation.orgName WHEN ApplParticipation.orgId<>1 and UPPER(Form.formLanguage)='F' THEN Organization.nameFrench WHEN ApplParticipation.orgId<>1 and UPPER(Form.formLanguage)='E' THEN Organization.nameEnglish ELSE '' END as COMP, Form.formLanguage) from hibernate.Form as Form INNER JOIN ApplParticipation as ApplParticipation on Form.application.applId = ApplParticipation.applId, hibernate.ApplParticipation LEFT OUTER JOIN OrgDeptLocation as OrgDeptLocation on ApplParticipation.orgDeptLocId = OrgDeptLocation.orgDeptLocId, hibernate.ApplParticipation LEFT OUTER JOIN Organization as Organization on ApplParticipation.orgId = Organization.orgId where (Form.formId = :formId) and (ApplParticipation.roleCode in (148,149)) order by 20 ASC, 8 ASC, 9 ASC, 2 ASC]

at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)

at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)

at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)

at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)

at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427)

at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)

at org.hibernate.impl.SessionImpl.list(SessionImpl.java:834)

at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)

at ca.sshrc.web.forms.beans.programs.ParticipantBean.retrieve(ParticipantBean.java:157)

at ca.sshrc.web.forms.beans.programs.ParticipantBean.<init>(ParticipantBean.java:61)

at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)

at java.lang.reflect.Constructor.newInstance(Constructor.java:274)

at java.lang.Class.newInstance0(Class.java:308)

at java.lang.Class.newInstance(Class.java:261)

at java.beans.Beans.instantiate(Beans.java:204)

at java.beans.Beans.instantiate(Beans.java:48)

at com.sun.faces.config.ManagedBeanFactory.newInstance(ManagedBeanFactory.java:203)

at com.sun.faces.application.ApplicationAssociate.createAndMaybeStoreManagedBeans(ApplicationAssociate.java:256)

at com.sun.faces.el.VariableResolverImpl.resolveVariable(VariableResolverImpl.java:78)

at com.sun.faces.el.impl.NamedValue.evaluate(NamedValue.java:125)

at com.sun.faces.el.impl.ComplexValue.evaluate(ComplexValue.java:146)

at com.sun.faces.el.impl.ExpressionEvaluatorImpl.evaluate(ExpressionEvaluatorImpl.java:243)

at com.sun.faces.el.ValueBindingImpl.getValue(ValueBindingImpl.java:173)

at com.sun.faces.el.ValueBindingImpl.getValue(ValueBindingImpl.java:154)

at javax.faces.component.UIOutput.getValue(UIOutput.java:147)

at com.sun.faces.renderkit.html_basic.HtmlBasicInputRenderer.getValue(HtmlBasicInputRenderer.java:82)

at com.sun.faces.renderkit.html_basic.HtmlBasicRenderer.getCurrentValue(HtmlBasicRenderer.java:191)

at com.sun.faces.renderkit.html_basic.HtmlBasicRenderer.encodeEnd(HtmlBasicRenderer.java:169)

at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:712)

at javax.faces.webapp.UIComponentTag.encodeEnd(UIComponentTag.java:616)

at javax.faces.webapp.UIComponentTag.doEndTag(UIComponentTag.java:539)

at com.sun.faces.taglib.html_basic.InputHiddenTag.doEndTag(InputHiddenTag.java:178)

at org.apache.jsp.JSP.Programs.ParticipantBody_jsp._jspx_meth_h_inputHidden_0(ParticipantBody_jsp.java:512)

at org.apache.jsp.JSP.Programs.ParticipantBody_jsp._jspx_meth_f_subview_0(ParticipantBody_jsp.java:211)

at org.apache.jsp.JSP.Programs.ParticipantBody_jsp._jspService(ParticipantBody_jsp.java:152)

at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)

at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)

at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)

at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:704)

at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:590)

at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:510)

at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:966)

at org.apache.jasper.runtime.PageContextImpl.include(PageContextImpl.java:581)

at ca.sshrc.web.common.tags.templates.GetTag.doStartTag(GetTag.java:47)

at org.apache.jsp.JSP.layouts.ProgramLayout_jsp._jspx_meth_template_get_2(ProgramLayout_jsp.java:977)

at org.apache.jsp.JSP.layouts.ProgramLayout_jsp._jspx_meth_htm_td_5(ProgramLayout_jsp.java:942)

at org.apache.jsp.JSP.layouts.ProgramLayout_jsp._jspx_meth_htm_tr_1(ProgramLayout_jsp.java:850)

at org.apache.jsp.JSP.layouts.ProgramLayout_jsp._jspx_meth_htm_table_1(ProgramLayout_jsp.java:814)

at org.apache.jsp.JSP.layouts.ProgramLayout_jsp._jspx_meth_h_form_0(ProgramLayout_jsp.java:421)

at org.apache.jsp.JSP.layouts.ProgramLayout_jsp._jspx_meth_htm_body_0(ProgramLayout_jsp.java:344)

at org.apache.jsp.JSP.layouts.ProgramLayout_jsp._jspService(ProgramLayout_jsp.java:167)

at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)

at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)

at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)

at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:704)

at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:590)

at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:510)

at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:966)

at org.apache.jasper.runtime.PageContextImpl.include(PageContextImpl.java:581)

at ca.sshrc.web.common.tags.templates.InsertTag.doEndTag(InsertTag.java:23)

at org.apache.jsp.JSP.Programs.Participant_jsp._jspx_meth_template_insert_0(Participant_jsp.java:171)

at org.apache.jsp.JSP.Programs.Participant_jsp._jspx_meth_f_view_0(Participant_jsp.java:115)

at org.apache.jsp.JSP.Programs.Participant_jsp._jspService(Participant_jsp.java:80)

at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:94)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:324)

at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:292)

at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:236)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)

at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:704)

at org.apache.catalina.core.ApplicationDispatcher.processRequest(ApplicationDispatcher.java:474)

at org.apache.catalina.core.ApplicationDispatcher.doForward(ApplicationDispatcher.java:409)

at org.apache.catalina.core.ApplicationDispatcher.forward(ApplicationDispatcher.java:312)

at com.sun.faces.context.ExternalContextImpl.dispatch(ExternalContextImpl.java:322)

at com.sun.faces.application.ViewHandlerImpl.renderView(ViewHandlerImpl.java:130)

at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:87)

at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:200)

at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:117)

at javax.faces.webapp.FacesServlet.service(FacesServlet.java:198)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)

at ca.sshrc.web.common.services.filters.GZIPFilter.doFilter(GZIPFilter.java:20)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)

at ca.sshrc.web.common.services.filters.NavigationFilter.doFilter(NavigationFilter.java:124)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)

at ca.sshrc.web.common.services.filters.SessionExpiredFilter.doFilter(SessionExpiredFilter.java:91)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)

at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)

at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)

at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)

at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)

at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)

at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)

at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:300)

at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:374)

at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:743)

at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:675)

at org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:866)

at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)

at java.lang.Thread.run(Thread.java:534)

Caused by: line 1:1006: unexpected token: on

at org.hibernate.hql.antlr.HqlBaseParser.fromJoin(HqlBaseParser.java:1380)

at org.hibernate.hql.antlr.HqlBaseParser.fromClause(HqlBaseParser.java:1053)

at org.hibernate.hql.antlr.HqlBaseParser.selectFrom(HqlBaseParser.java:759)

at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:611)

at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:263)

at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:150)

at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:209)

... 126 more

my wild guess is that you try to send SQL query to session method that expects HQL query.

If you need to send SQL query then use session.createSQLQuery method
http://www.hibernate.org/hib_docs/v3/re ... rysql.html

No, this is an HQL query that worked perfectly fine until I had to upgrade to H3.0.5. I had to upgrade from 2.1.4 because I needed the CASE WHEN support from H3.0.4.

Now, the H3.0.5 SQL parser doesn't support the *=, =* operands in the WHERE clause. That's why I'm now trying to make this HQL work with the ANSI syntax.


Cheers.

Then just try removing on clauses because H expects those associations being already defined in mapping files.
It looks like H3 does not support arbitrary joins.

I suggest resorting to native SQL at least for time being.

H2 passed much of the items fater the select clause straight the SQL engine.
This aspect is much more strict now that we are using a better (formally defined language) parser.

Only do your outer joins on a mapped relationship. You need to use native SQL if you don't have those particular relationships mapped.

Thanks for your reply.

I'll brush up on the association mapping. In the mean time, could you tell me if the ON clause should work in H3?

Thx

ON clause does not supported in H3. That is pretty much what the error says: H3 HQL parser does not expect ON. When an association is mapped in hbm files then H3 automatically generates necessary SQL, hense the shortened syntax ( O1 |some| join O2 )

Thanks for your reply Konstantin.


Would you do me a favor and send we the following From clause syntax in ANSI/H3. Assume the necessary H3 mapping is there. I tried a bunch of possibilities and I always get syntax errors.

I'm trying to join FORMS to APPLPARTICIPATION and then join APPLPARTICIPATION to two tables, ORGANIZATION and ORGDEPTLOCATION to get the organization name and the organization department location. I tried parentheses and comma but nothing works.
I can't find any examples that do more then joining two tables.

Thanks in advance

From clause:
from Form as Form INNER JOIN ApplParticipation as ApplParticipation
ApplParticipation LEFT OUTER JOIN OrgDeptLocation as OrgDeptLocation
ApplParticipation LEFT OUTER JOIN Organization as Organization

Cheers

I would normally expect to see some FETCH operands in this query as well but in anycase you should get the idea.

Oooh! So there's "." between the classes you wanna join. Nice to know, I'm trying it right away.

Thanks

no, the "." seperates associations to which you want to join.

for example, consider the normal order -> customer association:


HQL:


HQL is defined in terms of classes/properties/associations. HQL joins, in particular, are defined in terms of mapped associations.