Hi everyone!
I have a problem with a NHibernate query with sub-query for a report.
I have a Native SQL Query (SQLServer) with nested sub query and I need convert to NHibernate 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 NHibernate?
Thanks and greetings!
Login
FAQ
Search