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("SE LECT * 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.
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("SE
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
:-)
ASKER
btw, could I have done this using hql instead?
ASKER
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)
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)
ASKER
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
btw, the question is posted at https://www.experts-exchange.com/questions/21380535/hibernate-translating-a-query-containing-in-from-sql-to-hql.html
ASKER