[nhusers] Problem with NHibernate HQL Query and SubQuery

[zsamer]

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:

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:

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!

-- 
You received this message because you are subscribed to the Google Groups 
"nhusers" group.
To post to this group, send email to nhusers@googlegroups.com.
To unsubscribe from this group, send email to 
nhusers+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/nhusers?hl=en.