Problem HQL com subqueries

Boa noite.

Estou com um sério problema no Hibernate.
Preciso que valores de diferentes subqueries sejam agrupados em uma unica subquery para retornar um objeto de uma classe que tenho.

Em SQL Server tenho o costume de fazer coisas do tipo:

Pensei que o Hibernate seguia a mesma lógica, então fiz a seguinte função:

public String constroeQueries(String id){ return "SELECT NEW ResultadoRelatorioDisciplinar( "+ "(SELECT COUNT(r) FROM RegistroDisciplina AS r LEFT JOIN r.aluno AS a LEFT JOIN r.turma AS t LEFT JOIN t.disciplina AS d LEFT JOIN d.tipo AS p WHERE a.id="+id+" AND r.status='Aprovado' AND p.tipo='Obrigatória') AS qntObrigatoriasAprovadas "+ ",(SELECT SUM(d.cargaHoraria)FROM RegistroDisciplina AS r LEFT JOIN r.aluno AS a LEFT JOIN r.turma AS t LEFT JOIN t.disciplina AS d LEFT JOIN d.tipo AS p WHERE a.id="+id+" AND r.status='Aprovado' AND p.tipo='Obrigatória') AS qntHorasObrigatoriasAprovadas "+ ",(SELECT COUNT(r) FROM RegistroDisciplina AS r LEFT JOIN r.aluno AS a LEFT JOIN r.turma AS t LEFT JOIN t.disciplina AS d LEFT JOIN d.tipo AS p WHERE a.id="+id+" AND r.status='Aprovado' AND p.tipo='Optativa') AS qntOptativasAprovadas "+ ",(SELECT SUM(d.cargaHoraria)FROM RegistroDisciplina AS r LEFT JOIN r.aluno AS a LEFT JOIN r.turma AS t LEFT JOIN t.disciplina AS d LEFT JOIN d.tipo AS p WHERE a.id="+id+" AND r.status='Aprovado' AND p.tipo='Optativa') AS qntHorasOptativasAprovadas "+ ",(SELECT COUNT(r) FROM RegistroDisciplina AS r LEFT JOIN r.aluno AS a LEFT JOIN r.turma AS t LEFT JOIN t.disciplina AS d LEFT JOIN d.tipo AS p WHERE a.id="+id+" AND r.status='Aprovado' AND p.tipo='Eletiva') AS qntEletivaAprovadas "+ ",(SELECT SUM(d.cargaHoraria)FROM RegistroDisciplina AS r LEFT JOIN r.aluno AS a LEFT JOIN r.turma AS t LEFT JOIN t.disciplina AS d LEFT JOIN d.tipo AS p WHERE a.id="+id+" AND r.status='Aprovado' AND p.tipo='Eletiva') AS qntHorasEletivasAprovadas "+ ",(SELECT COUNT(r) FROM RegistroDisciplina AS r LEFT JOIN r.aluno AS a LEFT JOIN r.turma AS t LEFT JOIN t.disciplina AS d LEFT JOIN d.tipo AS p WHERE a.id="+id+" AND r.status='Em Curso' AND p.tipo='Obrigatória') AS qntObrigatoriasEmCurso "+ ",(SELECT SUM(d.cargaHoraria) FROM RegistroDisciplina AS r LEFT JOIN r.aluno AS a LEFT JOIN r.turma AS t LEFT JOIN t.disciplina AS d LEFT JOIN d.tipo AS p WHERE a.id="+id+" AND r.status='Em Curso' AND p.tipo='Obrigatória') AS qntHorasObrigatoriasEmCurso "+ ",(SELECT COUNT(r) FROM RegistroDisciplina AS r LEFT JOIN r.aluno AS a LEFT JOIN r.turma AS t LEFT JOIN t.disciplina AS d LEFT JOIN d.tipo AS p WHERE a.id="+id+" AND r.status='Em Curso' AND p.tipo='Optativa') AS qntOptativasEmCurso "+ ",(SELECT SUM(d.cargaHoraria) FROM RegistroDisciplina AS r LEFT JOIN r.aluno AS a LEFT JOIN r.turma AS t LEFT JOIN t.disciplina AS d LEFT JOIN d.tipo AS p WHERE a.id="+id+" AND r.status='Em Curso' AND p.tipo='Optativa') AS qntHorasOptativasEmCurso "+ ",(SELECT COUNT(r) FROM RegistroDisciplina AS r LEFT JOIN r.aluno AS a LEFT JOIN r.turma AS t LEFT JOIN t.disciplina AS d LEFT JOIN d.tipo AS p WHERE a.id="+id+" AND r.status='Em Curso' AND p.tipo='Eletiva') AS qntEletivasEmCurso "+ ",(SELECT SUM(d.cargaHoraria)FROM RegistroDisciplina AS r LEFT JOIN r.aluno AS a LEFT JOIN r.turma AS t LEFT JOIN t.disciplina AS d LEFT JOIN d.tipo AS p WHERE a.id="+id+" AND r.status='Em Curso' AND p.tipo='Eletiva') AS qntHorasEletivasEmCurso "+ ",(SELECT c.quantDisciplinasObrigatorias FROM Aluno As a LEFT JOIN a.curso as C WHERE a.id="+id+") as qntObrigatorias "+ ",(SELECT c.quantHorasDisciplinasObrigatorias FROM Aluno As a LEFT JOIN a.curso as C WHERE a.id="+id+") as qntHorasObrigatorias "+ ",(SELECT c.quantHorasDisciplinasOptativas FROM Aluno As a LEFT JOIN a.curso as C WHERE a.id="+id+") as qntHorasOptativas "+ ",(SELECT c.quantHorasDisciplinasEletivas FROM Aluno As a LEFT JOIN a.curso as C WHERE a.id="+id+") as qntHorasEletivas)"; }

No entanto, ao executar esta query eu recebo:

ERROR: <AST>:0:0: unexpected end of subtree
ERROR: <AST>:0:0: unexpected end of subtree
<AST>:0:0: unexpected end of subtree
	at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3185)
	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:248)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:183)
	at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:105)
	at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
	at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:168)
	at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:219)
	at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:197)
	at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1732)
	at br.com.dao.GenericDAOImpl.buscaByQuery(GenericDAOImpl.java:115)
	at br.com.dao.RecuperaDAO.getRRD(RecuperaDAO.java:216)
	at br.com.mbeans.RelatorioDisciplinarMB.getEstatisticas(RelatorioDisciplinarMB.java:84)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:601)
	at javax.el.BeanELResolver.getValue(BeanELResolver.java:363)
	at com.sun.faces.el.DemuxCompositeELResolver._getValue(DemuxCompositeELResolver.java:176)
	at com.sun.faces.el.DemuxCompositeELResolver.getValue(DemuxCompositeELResolver.java:203)
	at com.sun.el.parser.AstValue.getValue(AstValue.java:138)
	at com.sun.el.parser.AstValue.getValue(AstValue.java:183)
	at com.sun.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:224)
	at com.sun.faces.facelets.el.TagValueExpression.getValue(TagValueExpression.java:109)
	at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:194)
	at javax.faces.component.ComponentStateHelper.eval(ComponentStateHelper.java:182)
	at javax.faces.component.UIData.getValue(UIData.java:731)
	at javax.faces.component.UIData.getDataModel(UIData.java:1798)
	at javax.faces.component.UIData.setRowIndexWithoutRowStatePreserved(UIData.java:484)
	at javax.faces.component.UIData.setRowIndex(UIData.java:473)
	at com.sun.faces.renderkit.html_basic.TableRenderer.encodeBegin(TableRenderer.java:81)
	at javax.faces.component.UIComponentBase.encodeBegin(UIComponentBase.java:820)
	at javax.faces.component.UIData.encodeBegin(UIData.java:1118)
	at org.primefaces.renderkit.CoreRenderer.renderChild(CoreRenderer.java:54)
	at org.primefaces.renderkit.CoreRenderer.renderChildren(CoreRenderer.java:45)
	at org.primefaces.component.fieldset.FieldsetRenderer.encodeContent(FieldsetRenderer.java:86)
	at org.primefaces.component.fieldset.FieldsetRenderer.encodeMarkup(FieldsetRenderer.java:68)
	at org.primefaces.component.fieldset.FieldsetRenderer.encodeEnd(FieldsetRenderer.java:45)
	at javax.faces.component.UIComponentBase.encodeEnd(UIComponentBase.java:875)
	at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1764)
	at javax.faces.render.Renderer.encodeChildren(Renderer.java:168)
	at javax.faces.component.UIComponentBase.encodeChildren(UIComponentBase.java:845)
	at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1757)
	at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1760)
	at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1760)
	at com.sun.faces.application.view.FaceletViewHandlingStrategy.renderView(FaceletViewHandlingStrategy.java:402)
	at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:131)
	at com.sun.faces.lifecycle.RenderResponsePhase.execute(RenderResponsePhase.java:121)
	at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:101)
	at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:139)
	at javax.faces.webapp.FacesServlet.service(FacesServlet.java:594)
	at org.apache.catalina.core.StandardWrapper.service(StandardWrapper.java:1550)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:281)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175)
	at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:655)
	at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:595)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:161)
	at org.apache.catalina.connector.CoyoteAdapter.doService(CoyoteAdapter.java:331)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:231)
	at com.sun.enterprise.v3.services.impl.ContainerMapper$AdapterCallable.call(ContainerMapper.java:317)
	at com.sun.enterprise.v3.services.impl.ContainerMapper.service(ContainerMapper.java:195)
	at com.sun.grizzly.http.ProcessorTask.invokeAdapter(ProcessorTask.java:860)
	at com.sun.grizzly.http.ProcessorTask.doProcess(ProcessorTask.java:757)
	at com.sun.grizzly.http.ProcessorTask.process(ProcessorTask.java:1056)
	at com.sun.grizzly.http.DefaultProtocolFilter.execute(DefaultProtocolFilter.java:229)
	at com.sun.grizzly.DefaultProtocolChain.executeProtocolFilter(DefaultProtocolChain.java:137)
	at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:104)
	at com.sun.grizzly.DefaultProtocolChain.execute(DefaultProtocolChain.java:90)
	at com.sun.grizzly.http.HttpProtocolChain.execute(HttpProtocolChain.java:79)
	at com.sun.grizzly.ProtocolChainContextTask.doCall(ProtocolChainContextTask.java:54)
	at com.sun.grizzly.SelectionKeyContextTask.call(SelectionKeyContextTask.java:59)
	at com.sun.grizzly.ContextTask.run(ContextTask.java:71)
	at com.sun.grizzly.util.AbstractThreadPool$Worker.doWork(AbstractThreadPool.java:532)
	at com.sun.grizzly.util.AbstractThreadPool$Worker.run(AbstractThreadPool.java:513)
	at java.lang.Thread.run(Thread.java:722)

       //... Mais um milhão de linhas de exceções

Alguém sabe uma forma de fazer isto que eu quero?

Querys complexas prefira fazer via SQL nativo.

Gostaria de uma solução em HQL se alguem souber.
Só se realmente não tiver jeito pretendo fazer com SQL.

De qualquer forma, Obrigado! :smiley:

Não sei se vou ajudar muito, mas eu obtive esse mesmo erro esses dias quando eu fazia algo assim:

"select t from Tabela t where t.subTabela in (" + montarAlgoDinamico() + ") and t.id > 5;";

e este metodo retornava vazio …

ai ficava assim:

"select t from Tabela t where t.subTabela in () and t.id > 5;";

e … bumm… esse erro.

Tua query ta mto grande, dificil cara, tudo numa linha…
Separa melhor isso ai…
de preferencia usa StringBuilder vai dando uns appends e tal…

Tendi. Vou verificar o QueryBuilder, talvez saí o que eu quero.
Valeu aí!

[quote=fernandotnl]Tendi. Vou verificar o QueryBuilder, talvez saí o que eu quero.
Valeu aí![/quote]

StringBuilder … foi o q falei :slight_smile:

Mas QueryBuilder tbem rola :stuck_out_tongue:

Haha. Verdade.
Verei os dois.
:smiley: