I’ve two tables namely deliverydetails and supplybilldetails with following SQL create statements
Code:
[color=#0000BF]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;[/color]
Code:
DROP TABLE IF EXISTS `labinv4`.`supplybilldetail`;
[color=#008000]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;
[/color]
Their respective classes are
Code:
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……..
Code:
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????
Login
FAQ
Search