• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

help with left outer join in Hibernate

 
Pankaj Singla
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I am using struts with hibernate2. I want to do a left outer join in HQL but I am not able to form hql properly. I have two classes Voucher and CommodityInfo with purchaseReqNo filed in Voucher as foreign key in CommodityInfo. I am writing both sql and hql version of what I am doing. While sql gives the correct results, HQL gives the total sum instead of grouping by PurchaseReqNo.

Could someone tell me what I am doing wrong.
Any help will be appreciated.

The mapping in commodityInfo is:

<many-to-one
name="voucher"
class="org.oemc.model.Voucher"
cascade="none"
outer-join="true"
update="false"
insert="false"
access="property"
column="purchaseReqNo"
/>

SQL
------
select V.purchasereqno,v.vendorname,v.justification,
v.datecreated,sum(c.amount)
from voucher v, commodityinfo c
where v.purchasereqno=c.purchasereqno(+)
group by c.purchasereqno,v.justification,v.datecreated,v.vendorname

HQL
------
String sql = "select new Voucher (v.purchasereqno,v.vendorname,v.justification, v.datecreated,sum(c.amount)) " +
" from Voucher v, " +
" CommodityInfo c left outer join c.purchaseReqNo as purchaserReqNo " +
" group by v.purchasereqno,v.vendorname,v.justification,
v.datecreated,sum(c.amount)";

return getHibernateTemplate().find(sql);

Thanks,

Pankaj
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic