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.
LVL 1
aturetskyAsked:
Who is Participating?
 
aozarovConnect With a Mentor Commented:
you need to provide alias for the table via "{...}" and then use that alias name as the second argument of the function.
Something like that:

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();

see: http://forum.hibernate.org/viewtopic.php?t=418
0
 
aturetskyAuthor Commented:
you the man
0
 
aozarovCommented:
:-)
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
aturetskyAuthor Commented:
btw, could I have done this using hql instead?
0
 
aturetskyAuthor Commented:
I am posting this as another question
0
 
aozarovCommented:
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)
0
 
aturetskyAuthor Commented:
yeah, but this one contains an "in" keyword which hql does not recognize
btw, the question is posted at http://www.experts-exchange.com/Programming/Programming_Languages/Java/Q_21380535.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.