Hi,

I am using hibernate2. I want
to do a left outer join but I am not able to form hql
properly. 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.

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