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.
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)";
What are you doing? You are supposed to be reading this tiny ad!
the new thread boost feature brings a LOT of attention to your favorite threads