I need to execute an outerjoin but I cannot map the relation between my 2 tables, since the outer join is not on a primary key and not even on an equality.
E.g. my query (translated in SQL) should be like this:

select *
from (person p left outer join city c
on (p.id_city = c.hcod) and
(p.born_date > c.start_date) and
((p.born_date < c.end_date) or (c.end_date is null)))

But HCOD is NOT the PrimaryKey of CITY.
Is this possible?
If not, do you plan to add support?
I really need something like that, otherwise I must use a UNION
and my queries will become huge.

Thanks for any help.
Alessandro Rizzi

arbitary outer joins in HQL are not possible yet. You can use createSqlQuery to workaround probably.

Are they planned for the future?
I really need it.
Alessandro Rizzi

I had the same problem, but with createSQLQuery also i am not able to get. I need to get the data from multiple tables.
My Query is
mQuery = "select {smvo}.spmaterialidvc as {smvo.specialMaterialID},{smvo}.spmaterialnamevc as {smvo.displayName},{smvo}.plantidin as {smvo.plantID},{pvo}.plantnamevc as {pvo.plantName},{smvo}.customeridvc as {smvo.customerID},{cvo}.customernamevc as {cvo.customerName},{smvo}.vendoridvc as {smvo.vendorID},{vvo}.vendornamevc as {vvo.vendorName} from plant as {pvo}, customer as {cvo}, specialmaterial as {smvo} left outer join vendor as {vvo} on smvo.vendoridvc = vvo.vendoridvc where smvo.spmaterialidvc=:specialMaterialID and {smvo}.plantidin={pvo}.plantidin and smvo.customeridvc=cvo.customeridvc";

String[] aliases = {"smvo","pvo","cvo","vvo"};
Class[] loadclass = {SpecialMaterialVO.class,PlantVO.class,CustomerVO.class,VendorVO.class};
Query mSpecialMatQuery = mSession.createSQLQuery(mQuery,aliases,loadclass);
mSpecialMatQuery.setString("specialMaterialID","1234");
List myList = mSpecialMatQuery.list();

What type of object instances will be returned in the List myList? I am not able to type cast it.

I am also having the same problem. Here's a part of my code ...

StringBuffer sql = new StringBuffer();
sql.append(" SELECT {A.*} ");
sql.append(" FROM RTBEPAM.RTBEPAMAGENTS {A}, RTBEPAM.RTBEPAMIDENTITYAGENTS {IA} ");
sql.append(" WHERE ROWNUM<=10 ");
sql.append(" AND {A}.DOMAIN = '"+domain+"' ");
sql.append(" AND {A}.AGENTID = {IA}.AGENTID (+) ");
sql.append(" AND ({IA}.IDENTITYID<=0 OR {IA}.IDENTITYID IS NULL) ");
String[] aliases={"A","IA"};
Class[] classes={Rtbepamagents.class,Rtbepamidentityagents.class};
List agentsList = session.createSQLQuery(sql.toString(),aliases,classes).list();

Can anybody help me?

You get a List oj Object[], each representing one "row" and containing two objects. Please read the docs and use your debugger.