How to replicate this SQL querry

jagadeshk · **Joined:** Thu Jul 31, 2008 2:43 pm **Posts:** 2

How will i be able to replicate the following SQLquerry in hibernate.

SQL Querry
-------------
select main2.ID_ as ID from
(select distinct fv.Order_ID from order_functvalinstance fv where (fv.FUNCTION_= 'ABC' and fv.VALUE_ = '123' ) ) as main,

(select distinct fv.Order_ID from order_functvalinstance fv where (fv.FUNCTION_= 'xyz' and fv.VALUE_ = '891' ) ) as main2

where main.ID_=main2.ID_

Cameron McKenzie · **Posted:** Thu Jul 31, 2008 4:44 pm

Once you get your objects mapped, I think the Criteria API will make this a very easy request to implement:

How to Use the Hibernate Criteria API: A Simple Tutorial

jagadeshk · **Joined:** Thu Jul 31, 2008 2:43 pm **Posts:** 2

Hi Cameron McKenzie,
Thanks for your input. I am new to Hibernate so i dint try new ways.
I did the following and landed in Exception.

(A)I got my objects mapped,
(B) I tried to use the usual SQL like querry structure to feed the Hibernate.

I did something like this....

Query selectQuery = session.createQuery("select main.id from (select distinct fc.function , fc.id from com.exe.Function fc where fc.function='Add') main");

But i land up in exception as below....

ERROR ErrorCounter : line 1:21: unexpected token: (
ERROR ErrorCounter : line 1:58: unexpected token: from
ERROR ErrorCounter : line 1:91: expecting "by", found '.'

I am confused... Can you please tell me whether
1. I cant use a SQL querry as such a parameter for createQuery().
2. Will hibernate mandatorily expect a table name next to the "from " keyword.
3. Can the above SQL scenerio represented in some way in HQL.

And any of your valuble inputs for me.