-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 
Author Message
 Post subject: Problem with Hibernate HQL query and SubQuery
PostPosted: Fri Apr 08, 2011 7:11 pm 
Newbie

Joined: Fri Apr 08, 2011 7:07 pm
Posts: 2
Hi everyone!

I have a problem with a Hibernate query with sub-query for a report.

I have a Native SQL Query (SQLServer) with nested sub query and I need convert to Hibernate HQL.

The Native SQL query is:
Code:
SELECT
    ma.persona,
    ma.totalA,
    mb.totalB,
    (ma.totalA + mb.totalB) as totalAyB
    FROM
    (
    SELECT
    person.NomPersonal as persona,
    SUM(det.Cantidad * denom.Valor_Denominacion) as totalA
    FROM Transaccion AS td
    INNER JOIN tk_transaccion_detalle_certificado as det ON (td.Id_Transaccion = det.Id_Transaccion)
    INNER JOIN tk_servicio as serv ON (td.Id_Servicio = serv.Id_Servicio)
    INNER JOIN Tarjeta as tarj ON (tarj.Id_Tarjeta = td.Id_Tarjeta)
    INNER JOIN Personal as person ON (tarj.Id_Personal = person.Id_Personal)
    INNER JOIN Denominacion as denom ON (det.Cod_Denominacion = denom.Cod_Denominacion)
    INNER JOIN Servicio_Det as rem ON (det.Cod_Remesa = rem.Cod_Remesa)
    WHERE serv.Id_Servicio = '3001222320112453'
    AND rem.Ubicacion = 'A'
    GROUP BY person.NomPersonal  ) as ma
INNER JOIN
        (
    SELECT
    person.NomPersonal as persona,
    SUM(det.Cantidad * denom.Valor_Denominacion) as totalB
    FROM Transaccion AS td
    INNER JOIN tk_transaccion_detalle_certificado as det ON (td.Id_Transaccion = det.Id_Transaccion)
    INNER JOIN tk_servicio as serv ON (td.Id_Servicio = serv.Id_Servicio)
    INNER JOIN Tarjeta as tarj ON (tarj.Id_Tarjeta = td.Id_Tarjeta)
    INNER JOIN Personal as person ON (tarj.Id_Personal = person.Id_Personal)
    INNER JOIN Denominacion as denom ON (det.Cod_Denominacion = denom.Cod_Denominacion)
    INNER JOIN Servicio_Det as rem ON (det.Cod_Remesa = rem.Cod_Remesa)
    WHERE serv.Id_Servicio = '3001222320112453'
    AND rem.Ubicacion = 'B'
    GROUP BY person.NomPersonal  ) as mb ON (ma.persona = mb.persona)


The idea is to transform in HQL, but it does not work, so far I have the following HQL:

Code:
select
    ma.persona,
    ma.totalA,
    mb.totalB,
    (ma.totalA + mb.totalB) as totalAyB
    from
    (
    select person.NomPersonal as persona, sum(det.Cantidad * denom.Valor) as totalA from Transaccion td
    inner join td.TransaccionDetalles as det
    join td.Tarjeta as tarj
    join det.Denominacion as denom
    join det.Remesa as rem 
    join tarj.Persona as person 
    where td.Servicio.Id = :servicioId
    and rem.Ubicacion = 'A'
    group by person.NomPersonal
    order by person.NomPersonal desc) as ma
   join
        (   
    select person.NomPersonal as persona, sum(det.Cantidad * denom.Valor) as totalB from Transaccion td
    inner join td.TransaccionDetalles as det
    join td.Tarjeta as tarj
    join det.Denominacion as denom
    join det.Remesa as rem 
    join tarj.Persona as person 
    where td.Servicio.Id = :servicioId
    and rem.Ubicacion = 'B'
    group by person.NomPersonal
    order by person.NomPersonal desc) as mb ON (ma.persona = mb.persona)



Any idea how it could be converted to Hibernate?

Thanks and greetings!


Top
 Profile  
 
 Post subject: Re: Problem with Hibernate HQL query and SubQuery
PostPosted: Wed Jul 27, 2011 10:51 am 
Newbie

Joined: Wed Jul 27, 2011 10:31 am
Posts: 5
Hi zsamer,

Did you get the correct equivalent HQL for this? I'm facing the same problem.

Thanks,
alvagenesis


Top
 Profile  
 
 Post subject: Re: Problem with Hibernate HQL query and SubQuery
PostPosted: Wed Jul 27, 2011 1:40 pm 
Newbie

Joined: Fri Jul 22, 2011 5:04 pm
Posts: 8
I'm not sure this is possible. I would suggest issuing two queries (one for 'A' and one for 'B') and then combining the results in code:
Code:
select person.NomPersonal as persona, sum(det.Cantidad * denom.Valor) as total from Transaccion td
    inner join td.TransaccionDetalles as det
    join td.Tarjeta as tarj
    join det.Denominacion as denom
    join det.Remesa as rem 
    join tarj.Persona as person 
    where td.Servicio.Id = :servicioId
    and rem.Ubicacion = :ubicacion
    group by person.NomPersonal
    order by person.NomPersonal desc


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 3 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.