Hibernate version: 3.1

Hi there..

i´m having trouble trying to convert this SQL that is running on Oracle to HQL..

Seens like we cant use subselects in the from clause.. is that it?? is that a way to do that?? any workaround????

select os.sgOrgaosetor,
os.nmOrgaosetor ,
sum(saldoIni) saldoIni,
sum(recebidas) recebidas,
sum(encaminhadas) encaminhadas,
sum(saldoIni)+sum(recebidas)-sum(encaminhadas) as saldoFim
from (select T.cdOrgaotrami cdOrgaotrami,count(*) saldoIni,0 recebidas,0 encaminhadas
from ecpaTramitacao T
where (dtRecebto <= '20-fev-06' and (dtEncaminha >= '20-fev-06' or dtEncaminha is null))
group by t.cdOrgaotrami
union
select T.cdOrgaotrami cdOrgaotrami,0 saldoIni,count(*) recebidas,0 encaminhadas
from ecpaTramitacao T
where dtRecebto >= '20-fev-06' and (dtRecebto <= '01-mar-06')
group by t.cdOrgaotrami
union
select T.cdOrgaotrami cdOrgaotrami,0 saldoIni,0 recebidas,count(*) encaminhadas
from ecpaTramitacao T
where dtEncaminha >= '20-fev-06' and (dtEncaminha <= '01-mar-06')
group by t.cdOrgaotrami
)x,
ecpaOrgaosetor os
where os.cdorgaosetor = x.cdOrgaotrami
group by os.sgOrgaosetor,os.nmOrgaosetor
having sum(saldoIni) + sum(encaminhadas) +sum(recebidas) > 0



The Hql i tried is this:


select new br.com.softplan.sider.cpav.relatorio.RelTramitacaoSetor
( os.sgOrgaosetor,
os.nmOrgaosetor,
sum(saldoIni),
sum(recebidas),
sum(encaminhadas),
sum(saldoIni)+sum(recebidas)-sum(encaminhadas)
)
from ( select tramit.tramitacaoPK.cdOrgaotrami cdOrgaotrami,count(*) saldoIni,0 recebidas,0 encaminhadas
from Tramitacao tramit
where (tramit.dtRecebto < :periodoIni and (tramit.dtEncaminha >= :periodoIni or tramit.dtEncaminha is null))
group by tramit.tramitacaoPK.cdOrgaotrami
union
select tramit.tramitacaoPK.cdOrgaotrami cdOrgaotrami,0 saldoIni,count(*) recebidas,0 encaminhadas
from Tramitacao tramit
where tramit.dtRecebto >= :periodoIni and (tramit.dtRecebto < :periodoFim )
group by t.cdOrgaotrami
union
select tramit.tramitacaoPK.cdOrgaotrami cdOrgaotrami,0 saldoIni,0 recebidas,count(*) encaminhadas
from Tramitacao tramit
where tramit.dtEncaminha >= :periodoIni and (tramit.dtEncaminha < :periodoFim)
group by t.cdOrgaotrami
)as x,
Orgaosetor os
where os.orgaosetorPK.cdorgaosetor = x.cdOrgaotrami
group by os.sgOrgaosetor,os.nmOrgaosetor
having sum(saldoIni) + sum(encaminhadas) + sum(recebidas) > 0
order by os.sgOrgaosetor



And the error i get is:

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 9, column 9 [
select new br.com.softplan.sider.cpav.relatorio.RelTramitacaoSetor
( os.sgOrgaosetor,
os.nmOrgaosetor,
sum(saldoIni),
sum(recebidas),
sum(encaminhadas),
sum(saldoIni)+sum(recebidas)-sum(encaminhadas)
)
from ( select ...

No one can help with this??

Haw do I do that?

200 EUR, and I will convert it for you ;)

from Hibernate reference 14.12. Subqueries

Ok.. we cant use subqueries in the from clause...

so it worked out for me puting it in the select clause... here is how hql looks like now..

tks

hql.append(" select new br.com.softplan.sider.cpav.relatorio.RelTramitacaoSetor" +
"\n ( os.orgaosetorPK.cdOrgaosetor," +
"\n os.sgOrgaosetor, " +
"\n os.nmOrgaosetor, " +
"\n (select nvl(count(*),0)"+
"\n from Tramitacao tramit "+
"\n where (tramit.dtRecebto < :periodoIni and (tramit.dtEncaminha >= :periodoIni or tramit.dtEncaminha is null))" +
"\n and tramit.cdOrgaotrami = os.orgaosetorPK.cdOrgaosetor "+
"\n group by tramit.cdOrgaotrami" +
"\n ), " +
"\n (select nvl(count(*),0)"+
"\n from Tramitacao tramit "+
"\n where (tramit.dtRecebto < :periodoIni and (tramit.dtEncaminha >= :periodoIni or tramit.dtEncaminha is null)) "+
"\n and tramit.cdOrgaotrami = os.orgaosetorPK.cdOrgaosetor "+
"\n group by tramit.cdOrgaotrami " +
"\n ),"+
"\n (select nvl(count(*),0) "+
"\n from Tramitacao tramit "+
"\n where tramit.dtEncaminha >= :periodoIni and (tramit.dtEncaminha < :periodoFim) "+
"\n and tramit.cdOrgaotrami = os.orgaosetorPK.cdOrgaosetor "+
"\n group by tramit.cdOrgaotrami "+
"\n )" +
"\n ,0 " +
"\n ) "+
"\n from Orgaosetor os "
);