Hi,

I have to convert following complex SQL query into HQL query.
I am having major problem joining result of the subquery with another table.
can anyone please tell me how to do this----> [from orders o right outer join (select * from position where position.created_at > '2009-01-01 00:00:00.000')] inhibernate???

select distinct o.*
from orders o right outer join (select * from position where position.created_at > '2009-01-01 00:00:00.000') p
on p.position_id = o.position_id
union
select distinct o.*
from orders o right outer join (select * from transaction where transaction.close_timestamp > '2009-01-01 00:00:00.000') t
on o.position_id = t.position_id
union
select * from orders o
where o.created_at > '2009-01-01 00:00:00.000'


My hibernate mapping for above is

<class name="Position" table="POSITION" lazy="false">
<id name="id" column="POSITION_ID" type="java.lang.Integer">
<generator class="sequence">
<param name="sequence">main_sequence</param>
</generator>
</id>
<set name="transaction" inverse="false">
<key column="POSITION_ID"/>
<one-to-many class="Transaction"/>
</set>
<set name="orders" inverse="false">
<key column="POSITION_ID"/>
<one-to-many class="Order"/>
</set>
</class>

<class name="Transaction" table="TRANSACTION">
<id name="objectId" column="TRANSACTION_UUID">
<generator class="uuid.hex"/>
</id>
<property name="positionId" column="POSITION_ID"/>
</class>

<class name="Order" table="ORDER">
<id name="objectId" column="ORDER_UUID">
<generator class="uuid.hex"/>
</id>
<property name="positionId" column="POSITION_ID"/>
</class>

Any help is appriciated..

Cheers!

Kindly in hibernate or someone from hibernate team reply this..

Many thanks..

The right outer join in your query doesn't make a whole lot of sense since you are doing a distinct and only selecting the values from o.*

The right join will get all the rows where the two tables join on position_id, and all the other rows that are just in position/transaction. When the rows are only in position or transaction, the values for the order columns will be null. When the distinct is done, these will all be comibined into one row of null values.

I think this query will get you the same results, without the one row of null values:

SELECT DISTINCT o FROM Orders o
JOIN o.position p
WHERE p.createdAt > :date
UNION
SELECT DISTINCT o FROM Orders o
JOIN o.transaction t
WHERE t.transactionClose > :date
UNION
SELECT DISTINCT o FROM Orders o
WHERE o.createdAt > :date

thanks for your reply.

Yes point noted though I should have noted it myself! :)

By the way, I have changed the query for good. Its as follows:

select {o.*}
from orders {o}, position p, transaction t
where o.position_id = p.position_id
and p.position_id = t.position_id
and (o.created_at > :startTime
or p.created_at > :startTime
or t.close_timestamp > :startTime)
and o.account_id = :accountId
order by o.order_id

Any comments?

Thanks