Link to home
Start Free TrialLog in
Avatar of aturetsky
aturetsky

asked on

hibernate: translating a query containing "in" from sql to hql

Could I somehow write this using hql, instead - or something that would give me the same result.

rsList = session.createSQLQuery("SELECT {t.*}  FROM rfq_supplier_table {t} WHERE ({t}.supplier_number, {t}.responded_date) IN (SELECT m.supplier_number, MAX (m.responded_date) FROM rfq_supplier_table m WHERE m.rfq_id = 1364 GROUP BY m.supplier_number)", "t", RfqSupplier.class).list();
ASKER CERTIFIED SOLUTION
Avatar of aozarov
aozarov

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of aturetsky
aturetsky

ASKER

you're right, in fact, unlike what I thought, it supports "in" as well

Thanks again.
:-)
Avatar of Jim Cakalic
Hmm. I knew that. I thought you were looking for something more complicated since your SQL query was using expression lists in the membership condition. I'm not convinced that this will translate easily to HQL. I'd be interested in seeing a rewrite of the query as the actual answer to this question to prove me wrong.

Jim
This works:

List rsList = session.find("from RfqResponse rs where (rs.supplierNumber, rs.respondedDate) in (select rs1.supplierNumber, max(rs1.respondedDate) from RfqResponse rs1 where rs1.rfq.rfqId = 1364 group by rs1.supplierNumber)");
Good deal. I didn't think it would do that. I'll have to trust hibernate more :-)