Link to home
Start Free TrialLog in
Avatar of aturetsky
aturetsky

asked on

how to hibernate this somewhat complex SQL?

How do I create a hibernate equivalent of the following, which works in regular SQL (Oracle):

SELECT * 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)


When trying to do it this way:
rsList = session.createSQLQuery("SELECT * 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)", "rfqSupplier", RfqSupplier.class).list();

I get the following error:
error performing findBySQL

Now, you might argue that that's because I am referencing the actual tables and columns rather than their corresponding Java object equivalents - well, I tried that as well and got the same error.
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 the man
:-)
btw, could I have done this using hql instead?
I am posting this as another question
I don't see why not.
HQL supports both GROUP BY and sub queries.
see http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html (11.10. The group by clause and 11.8. Expressions)
yeah, but this one contains an "in" keyword which hql does not recognize
btw, the question is posted at https://www.experts-exchange.com/questions/21380535/hibernate-translating-a-query-containing-in-from-sql-to-hql.html