?
Solved

how to hibernate this somewhat complex SQL?

Posted on 2005-04-05
7
Medium Priority
?
736 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:aturetsky
  • 4
  • 3
7 Comments
 
LVL 15

Accepted Solution

by:
aozarov earned 2000 total points
ID: 13713498
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
 
LVL 1

Author Comment

by:aturetsky
ID: 13721787
you the man
0
 
LVL 15

Expert Comment

by:aozarov
ID: 13721814
:-)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:aturetsky
ID: 13729610
btw, could I have done this using hql instead?
0
 
LVL 1

Author Comment

by:aturetsky
ID: 13729734
I am posting this as another question
0
 
LVL 15

Expert Comment

by:aozarov
ID: 13729794
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
 
LVL 1

Author Comment

by:aturetsky
ID: 13729864
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
The viewer will learn how to implement Singleton Design Pattern in Java.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses
Course of the Month14 days, 7 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question