Hi,

I have the following sql server query that needs to be translated to hql. All of the tables in the query have relative hibernate mapping files. How would I write something like this in HQL?

thanks in advance!


select org.name,sum(CAST(e2.value as int))
from dat_element_instance e2,
dat_payment_element pe2,
dat_provider_involvement pi1,
dat_payment p2,
dat_provider prov,
dat_participant par,
dat_organization org
where pe2.payment_id in
(select p.id
from dat_payment p,
dat_payment_element pe,
dat_element_instance e1,
dat_stage_instance s,
dat_workflow_instance w,
dat_workflow_group wg
where p.id = pe.payment_id
and pe.element_id = e1.id
and p.stage_id = s.id
and s.workflow_id = w.id
and w.group_id = wg.id
and e1.code = 'DATE9'
and CONVERT(DATETIME,e1.value,103) >= getdate()-10
and CONVERT(DATETIME,e1.value,103) <= getdate()+10
and wg.company_id = 2)
and e2.id = pe2.element_id
and pe2.payment_id = p2.id
and pi1.provider_id = prov.participant_id
and prov.participant_id = par.organization_id
and par.organization_id = org.id
and pi1.id = p2.involvement_id
and e2.code = 'PAY4'
group by org.name

Change table name references to entity classes and column names to properties and you should have runnable HQL. The each row in the results will be an Object[] so you might want to use dynamic instantiation (SELECT new MyResultClass(org.name,sum(e2.value)) ...) for the result rows. You can find references to using "SELECT new" in the Hibernate manual or on this forum. You may need to revise your use of CAST and you might have issues with the date conversion stuff -- I've never tried a CAST in HQL and I don't use SQL Server.

Good luck,
Curtis ...