Hi All Gurus,

I have a problem with a left join definition:

I have this tables:
TABLE1
TABLE1_ID INTEGER4 NOT NULL - PK
OTHER FIELDS ...
TABLE2
TABLE1_ID Integer4 NOT NULL - Composite-PK1 many-to-one with TABLE1
SEQUENCE Integer4 NOT NULL - Composite-PK2
OTHER FIELDS ...
VIEW1 (SELECT OF MANY TABLES COLLECTING TABLE1_ID)
TABLE1_ID Integer4 NOT NULL - PK

I need to do a LEFT OUTER JOIN BETWEEN TABLE2 and VIEW but it doesn´t work to me. I think that my problem is in the hibernate VIEW definition. I have tried many configurations but the result of the query is allways the same:

java.lang.NullPointerException
at org.hibernate.hql.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:315)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3258)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3050)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:2928)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:686)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:542)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:279)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:227)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:218)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:158)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:105)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:74)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:53)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:71)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:108)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:88)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1540)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:292)
at $Proxy0.createQuery(Unknown Source)
at ...

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="Table1DTO" table="TABLE1">
<id name="table1Id" column="TABLE1_ID" type="java.lang.Integer" unsaved-value="any">
<generator class="assigned">
</generator>
</id>
<property ... />
<list name="table2Elements" lazy="false" inverse="true" cascade="all">
<key column="TABLE1_ID">
</key>
<index column="SEQUENCE"/>
<one-to-many class="Table2DTO"/>
</list>
</class>
</hibernate-mapping>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="Table2DTO" table="TABLE2">
<composite-id name="id" class="Table2PkDTO" unsaved-value="any">
<key-many-to-one name="carpeta" class="Table1DTO" column="TABLE1_ID"/>
<key-property name="sequence" type="java.lang.Integer" column="SEQUENCE"/>
</composite-id>
<property ... />
</class>
</hibernate-mapping>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="View1DTO" table="VIEW1">
<id name="table1ID" column="TABLE1_ID" type="java.lang.Integer" unsaved-value="null">
<generator class="foreign">
<param name="property">table1</param>
</generator>
</id>
<one-to-one name="table1" class="Table1DTO" cascade="none" outer-join="auto" constrained="false"/>
</class>
</hibernate-mapping>

Query q = session
.createQuery("SELECT ce from "
+ Table2DTO.class.getName()+ " as ce "
+ "INNER JOIN "
+ Table1DTO.class.getName()+ " as c "
+ "LEFT OUTER JOIN "
+ View1DTO.class.getName()+ " as cnd "
+ "where cnd.table1 IS NULL " ...


A LOT OF THANKS IN ADVANCE.

In ur many-to-one mapping
use : property-ref="sequence"
and try

Thanks,
Gopal
[please rate, if helpful]

It doesn't work. The relation between TABLA1 and TABLA2 work well. I think, that the problem may be in the VIEW1 definition.
Thank you for the answer.