Hibernate Sub-query inside case when statement
I am having a problem executing an HQL sub-process inside a case when statement.
here is my code:
select case when a.entityId is null then 'invalid' else
(select b.entityName from tblName b where b.entityId =a.entityId)
end from tblEntity a
Thanks for any help!
Ok, here's the full stack trace
ERROR org.hibernate.hql.PARSER - <AST>:0:0: unexpected AST node: query
Exception in thread "Thread-5" org.hibernate.hql.ast.QuerySyntaxException: unexpected AST node: query [select case when a.entityId is null then 'invalid' else
(select b.entityName from tblName b where b.entityId =a.entityId)
end from tblEntity a]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:261)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:185)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1651)
source to share
This feature is not supported in sleep mode. But there is a workaround. Create a view in the database and then use HQL to fetch the result via java.
create or replace force view VW_ENTITY_VALUE (entityValue) as select case where a.entityId is null and then invalid (select b.entityName from tblName b where b.entityId = a.entityId) end from tblEntity a;
From HQL documentation: http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-subqueries
14.13. Subqueries
For databases that support subqueries, Hibernate supports subqueries in queries. The subquery must be surrounded by parentheses (often by calling an SQL aggregation function). Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed.
from Cat as bold where fatcat.weight> (select avg (cat.weight) from DomesticCat cat) from DomesticCat as cat where cat.name = some (select name.nickName from Name as name) from Cat as cat where does not exist (from Cat as mate where mate.mate = cat) from DomesticCat as cat where cat.name is not in (select name.nickName from Name as name) select cat.id, (select max (kit.weight) from set cat.kitten) from Cat like a cat Note that HQL subqueries can only appear in select or where clauses.
source to share