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....

union is not allowed. Try to think in object rather than in relational model when doing your query.

I solved my problem by using a view.

hehe this works too ;-)