[Hibernate] SQL Query

From: Lionel (SPAMcoollATfreePOINTfr)
Date: 05/07/04


Date: Fri, 7 May 2004 17:31:06 +0200

Hi all

I would like to do the following oracle sql query with hibernate by using
session.createSQLQuery but I don't know how...

SELECT fabId, produitId, SUM(total) AS total FROM
(
    SELECT p.fab_id AS fabId, p.produit_id AS produitId,
SUM(p.penalite_Valeur) AS total
    FROM PFT_PENALITE p
    GROUP BY p.fab_id, p.produit_id

    UNION

    SELECT f.fab_id AS fabId, f.produit_id AS produitId,
SUM(f.fiche_Penalite)
    FROM PFT_FICHE f
    WHERE f.fiche_penalite >0
    GROUP BY f.fab_id, f.produit_id
)
GROUP BY fabId, produitId

Is it possible to do that ?
what to do with the alias parameter ?

thanks a lot for your help...

BTW: if someone sees another simplier way to do a union on 2 queries
followed by a group by, the solution is welcome....