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???

I'm trying to generate reports with jasper in netbeans 6.1 with Tomcat 6.0 Jasper Report 2.0.5. My code given below, runs well in Java desktop application but not in jsp nor does not give any error.
Why does it work in java desktop application but not in JSP?
Thanks in advance.
try{ //Parameters to be sent to report [B]Map m=new HashMap(); m.put("proName", "General"); m.put("spID", 37);[/B] //Source and Destination of Reports [B] String reportSource = "/report/report1.jrxml"; String reportDest = "/report/report1.html"; JasperReport jasperReport = JasperCompileManager.compileReport(reportSource);[/B] //Connection making [B]Class.forName("com.mysql.jdbc.Driver"); Connection con =DriverManager.getConnection("jdbc:mysql://localhost:3306/lisdb", "root", "samsung");[/B] //Filling Report and exporting [B] JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, m, con); JasperExportManager.exportReportToHtmlFile(jasperPrint, reportDest); JasperViewer.viewReport(jasperPrint); }[/B] //Catching Exceptions [B]catch (JRException e){ e.printStackTrace(); } catch (ClassNotFoundException ex){ ex.printStackTrace(); } catch (SQLException ex){ ex.printStackTrace(); }[/B]
[ November 10, 2008: Message edited by: umair naqvi ]