HQL query required with left outer join

[umair naqvi]

I have two tables namely deliverydetails and supplybilldetails with following SQL create statements
DROP TABLE IF EXISTS `labinv4`.`deliverydetails`; CREATE TABLE `labinv4`.`deliverydetails` ( `id` int(10) unsigned NOT NULL auto_increment, `deliveryid` int(10) unsigned NOT NULL, `qty` decimal(10,2) NOT NULL, `LotNo` varchar(20) default NULL, `Expiry` date default NULL, `ManDate` date default NULL, `billdetailid` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `FK_delivery_details` (`deliveryid`), KEY `FK_BillDetailid` (`billdetailid`), CONSTRAINT `FK_BillDetailid` FOREIGN KEY (`billdetailid`) REFERENCES `supplybilldetail` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_delivery` FOREIGN KEY (`deliveryid`) REFERENCES `delivery` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DROP TABLE IF EXISTS `labinv4`.`supplybilldetail`; CREATE TABLE `labinv4`.`supplybilldetail` ( `id` int(10) unsigned NOT NULL auto_increment, `billid` int(10) unsigned NOT NULL, `Qty` decimal(10,2) NOT NULL, `LotNo` varchar(20) default NULL, `ManDate` date default NULL, `Expiry` date default NULL, `itemdetailid` int(10) unsigned NOT NULL, `podetailid` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `FK_supplybilldetail_bill` (`billid`), KEY `FK_bill_item` (`itemdetailid`), KEY `FK_podetail_sbdetail` (`podetailid`), CONSTRAINT `FK_bill_item` FOREIGN KEY (`itemdetailid`) REFERENCES `itemdetails` (`iditemDetails`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_podetail_sbdetail` FOREIGN KEY (`podetailid`) REFERENCES `purchaseorderdetails` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `FK_supplybilldetail_bill` FOREIGN KEY (`billid`) REFERENCES `supplierbill` (`idinvVendorBill`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1;


Their respective classes are

public class Deliverydetails  implements java.io.Serializable {
     private Integer id;
     private Supplybilldetail supplybilldetail;
     private Delivery delivery;
     private BigDecimal qty;
     private String lotNo;
     private Date expiry;
     private Date manDate; 

getters and setters are omitted……..

public class Supplybilldetail  implements java.io.Serializable {
     private Integer id;
     private Itemdetails itemdetails;
     private Purchaseorderdetails purchaseorderdetails;
     private Supplierbill supplierbill;
     private BigDecimal qty;
     private String lotNo;
     private Date manDate;
     private Date expiry;
     private Set deliverydetailses = new HashSet(0);

getters and setters are omitted……..


Tables are connected with supplybilldetail.id  deliverydetails.billdetailid

Problem is that I need to find the HQL query for the group sum of deliverydetails.qty on deliverydetails.billdetailid but I simply can’t. Following query works

“from Supplybilldetail as s left outer join s.deliverydetailses as d group by s.id

I want to add something like
“ where s.qty > sum(d.qty) group by s.id “ can somebody help me out???

[Ninad Kulkarni]

UseCodeTags