HQL error: with-clause referenced two different from-clause elements
11,957
Try this:
select
t.id,
t.name,
s1.id,
s1.field_map_nm as src_field_map_nm,
s2.id,
s2.field_map_nm as tgt_field_map_nm
from Rule as t
left join t.src_field_map as s1
left join t.tgt_field_map as s2
I think you don't need the with
clause since you only use the FK, which is implicit anyway.
Your with
clause is actually referencing the same table id twice:
s1.id = t.src_field_map.id
since s1 = t.src_field_map
So your with clause translates to:
t.src_field_map.id = t.src_field_map.id
So, you can just remove the with
clause.
Author by
Douglas
Updated on June 04, 2022Comments
-
Douglas almost 2 years
I am getting started with Hibernate and I am using HQL, using some joins to retrieve data from the db but getting this error, any help on how to solve this is appreciated.
Field.hbm.xml file:
<id name="id" type="string"> <column name="field_map_cd" /> <generator class="assigned"></generator> </id>
Rule.hbm.xml file:
<many-to-one name="src_field_map" column="field_map_cd" not-null="true" insert="false" update="false"/> <many-to-one name="tgt_field_map" column="field_map_cd" not-null="true" insert="false" update="false"/>
HQL query:
select t.id, t.name, t.src_field_map.id, s1.field_map_nm as src_field_map_nm, t.tgt_field_map.id,s2.field_map_nm as tgt_field_map_nm from Rule as t left join t.src_field_map as s1 left join t.tgt_field_map as s2 with (s1.id = t.src_field_map.id and s2.id = t.tgt_field_map.id)
Stacktrace:
ERROR: with-clause referenced two different from-clause elements with-clause referenced two different from-clause elements at org.hibernate.hql.internal.ast.HqlSqlWalker.handleWithFragment(HqlSqlWalker.java:465) at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:413) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3858) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3644) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3522) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:706) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:562) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:299) at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:247) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:278) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:206) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158) at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:126) at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:88) at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:190) at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301) at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236) at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800) at Utilities.test.readXrefRule(test.java:67) at Utilities.test.main(test.java:171) org.hibernate.hql.internal.ast.QuerySyntaxException: with-clause referenced two different from-clause elements [select t.id, t.name, t.src_field_map.id, s1.field_map_nm as src_field_map_nm,t.tgt_field_map.id, s2.field_map_nm as tgt_field_map_nm, orm.entity.OS_Rule as t left join t.src_field_map as s1 left join t.tgt_field_map as s2 with (s1.id = t.src_field_map.id and s2.id = t.tgt_field_map.id )] at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91) at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:284) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:206) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158) at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:126) at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:88) at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:190) at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301) at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236) at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800) at Utilities.test.readRule(test.java:67) at Utilities.test.main(test.java:171)