I wonder if formula prop. have limitations about the number of the tables joined.

<class name= "A" table ="Area">
...
...
<property name = " latitude"
formula =" Select p.LATITUDE from Area a, Icao b, Place p where a.ICAO_ID = b.ICAO_ID and b.PLACE_ID = p.PLACE_ID" />..


In Oracle this query works, but with hibernate, it gives errors.

aaah sorry, I misjudged something
forget this post pls

I think I did all well, but still I have a problem.
I cant join 3 tables (as the scenario above) within hibernate formula.
Am I doing something wrong?

Use parantheses formular="( select ... )" and check the SQL log output from Hibernate. Your database has to support subqueries.

does Oracle supports sub-queries? I think it should

of course. just add parentheses

I think I couldnt identify the problem. It is not easy to handle by just adding parantheses.

It is about the query that hibernate generates is not the one I want.

Let me identify the problem again: Here is the illustration:


class A : ID, status, latitude,longitude

table A : ID, STATUS, B_ID
table B: ID, NAME, C_ID
table C: ID, LATITUDE, LONGITUDE

mapping file:
<class name = "A" table = "A"..
<id...
<property
<property name = "latitude"
formula = "(Select c.LATITUDE from B b, C c, A a where
b.C_ID = c.ID and a.B_ID ="thisA". b.ID)" ....
</property>
</class>

Meanly, I want to hibernate to generate this query:

Select a1.ID , a1.STATUS, (select c.LATITUDE from C c, B b, A a2 where
a2.ID = a1.ID and a2.B_ID = b.ID and b.C_ID = c.ID) from A a1;

I need to have a instance of A in sub-select query, otherwise subquery will return a list. I mean I want to say hibernate that "return the latitude of C where C's id is equal to B's C_ID and the B's id is the A instance of the main query) 's B_ID , by that way I can ban the sub query to return list.

I should handle this by writing a query in my DAO class, but I want everything to be handled in the mapping file.

I hope that now the problem is clear.

what about

<property name = " latitude"
formula =" Select p.LATITUDE from Icao b, Place p where ICAO_ID = b.ICAO_ID and b.PLACE_ID = p.PLACE_ID" />..

This doesnt work.

It generates :

Select a_.ID as ID , (Select p.LATITUDE from ICAO b, a_.PLACE a_.p where a_.ICAO_ID = b.ICAO_ID and b.PLACE_ID = p.PLACE_ID ) as f2_ from A a_

and this query gives "Missing right paranthesis error"???

The mistake is in the query that hibernate generates :

HibernateQuery: "Select a_.ID as ID , (Select p.LATITUDE from ICAO b, a_.PLACE a_.p where a_.ICAO_ID = b.ICAO_ID and b.PLACE_ID = p.PLACE_ID ) as f2_ from A a_ "

When I remove tha (a_.) from the PLACE p, it works!

ExpectedQuery: "Select a_.ID as ID , (Select p.LATITUDE from ICAO b,PLACE p where a_.ICAO_ID = b.ICAO_ID and b.PLACE_ID = p.PLACE_ID ) as f2_ from A a_ " Works Fine.

Hey hibernate team, why is going on while generate query?, why u add the (a_. before one of the tables)? What can I do to find a way of solution?
What am I doing wrong????
I know I must be missing something ...

I cant find the reason of that "idiot" query, I wonder if it is a bug

Man, just learn to read! You have to use parantheses!

<property name="foo" formula="( select foo )"/>

<property name = " latitude"
formula ="( Select p.LATITUDE from Icao b, Place p where ICAO_ID = b.ICAO_ID and b.PLACE_ID = p.PLACE_ID )" />

cristian, that is the matter; I am using the parantheses if u mean the ones above. The idiot query still exists -)

Is "Place" the name of the "outer" table? If yes, then don't specifiy it in the FROM clause of your subselect, but simply reference without an alias: "where b.PLACE_ID = PLACE_ID". Show us the full mapping for the class.