Hey I'm really clueless on the exception....

outer or full join must be followed by path expression

Can anyone tells me what's wrong with this HQL query?

FROM warehouse.to.InvRequest AS INVREQ
WHERE INVREQ.RequestID IN
(
SELECT INVREQITEM.ParentInvRequest.RequestID
FROM warehouse.to.inv_request_item AS INVREQITEM
WHERE
INVREQITEM.ParentInvRequest.RequestID=INVREQ.RequestID
AND
(
INVREQITEM.StatusID=5 OR INVREQITEM.StatusID=12
)
)

AND

(
INVREQ.RequestID IN
(
SELECT REQ1.RequestID FROM warehouse.to.InvRequest REQ1
JOIN warehouse.to.inv_request_item INVREQITEM1 WHERE REQ1.RequestID<1000
)
)


You see, the problem is with the highlighted JOIN statement - I removed it and the problem goes away.

NOTE: RequestID < 1000 criteria may look trivial - I simplify this for the sake of troubleshooting.

For those of you who're suspecting it has to do with mapping files...

Mapping file for "Inventory Request" (parent of "Inventory Request Item")
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="warehouse.to.InvRequest, WarehouseLib" table="inv_request">

<id name="RequestID" column="request_id" type="Int32" unsaved-value="0" >
<generator class="identity" />
</id>

<property name="RequestedBy" column="requested_by" type="Int32" />
<property name="DeliverTo" column="deliver_to" type="String" length="100" />
<property name="DeliverDate" column="deliver_date" type="DateTime" />
<property name="InvAdjust" column="inv_adjust" type="Boolean" />
<property name="Remarks" column="remarks" type="String" length="2147483647" />

<property name="CreateBy" column= "CreateBy" type="Int32"/>
<property name="CreateDate" column= "CreateDate" type="DateTime"/>
<property name="OwnerGroup" column= "OwnerGroupUIN" type="Int32"/>
<property name="LastUpdateBy" column= "LastUpdateBy" type="Int32"/>
<property name="LastUpdateDate" column= "LastUpdateDate" type="DateTime"/>

<set name="RequestItems" inverse="true" cascade="all" table="inv_request_item" lazy="false">
<key column="request_id"/>
<one-to-many class="warehouse.to.inv_request_item, WarehouseLib"/>
</set>

</class>
</hibernate-mapping>

And that for the child "Inventory Request Item" (table: inv_request_item) is as follows:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="warehouse.to.inv_request_item, WarehouseLib" table="inv_request_item">

<composite-id unsaved-value="none" >
<key-many-to-one name="ParentInvRequest" column="request_id" class="warehouse.to.InvRequest, WarehouseLib" />
<key-property name="ItemID" column= "item_id" type="Int32" />
</composite-id>

<property name="RequestedQty" column= "requested_qty" type="Decimal" />
<property name="RevisedQty" column="revised_qty" type="Decimal" />
<property name="ConfirmedQty" column= "confirmed_qty" type="Decimal" />
<property name="StatusID" column= "status_id" type="Int16" />
<property name="Remarks" column= "remarks" type="String" length="100" />

<property name="CreateBy" column= "CreateBy" type="Int32"/>
<property name="CreateDate" column= "CreateDate" type="DateTime"/>
<property name="LastUpdateBy" column= "LastUpdateBy" type="Int32"/>
<property name="LastUpdateDate" column= "LastUpdateDate" type="DateTime"/>
</class>
</hibernate-mapping>

the joined class should be specified by its collection "path" from the parent class, something like this:



Please have look to the Hibernate documentation regarding HQL.

cheers,
Radu

In HQL you cannot do arbitrary joins, only joins following an association property, so you have to write something like