1

I'm creating nested select view to be run in Jasper Netbeans report from JAVA DB , and need to distinct the grouped based on same below statement either by CASE injected inside the select or using if is workable inside JAVA DB or any other way, last I want to use record set as source query to run jasper report my code as follows

private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {
 try {
   String host1= "jdbc:derby://localhost:1527//home/mohamed/java- 
   progs/SPaccounting/accountsdb/accountsdb;create=true";
   String uName1="rootuser";
   String uPass1="1234a1234b";
   con1=DriverManager.getConnection(host1,uName1 ,uPass1);
   String sql1="select sum(journal_amount) AS DR ,0 as CR, journal_crname  AS 
   ACCNAME FROM JOURNAL
   GROUP BY journal_CRNAME UNION select 0 AS DR,sum(journal_amount) AS   CR,  
   journal_dbname AS ACCNAME FROM JOURNAL
   GROUP BY journal_DRNAME order by ACCNAME
   ";
   JasperFillManager.fillReport(jasperReport,param1,connect); //add param    
   if   required    
   String jrxmlFileName = "/home/mohamed/java-progs/accounting syst em/     
   JavaAaccountingsys/src/report1.jrxml";
   JasperDesign jasperDesign = JRXmlLoader.load(jrxmlFileName);


   JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);
   JasperPrint jasperPrint = JasperFillManager.fillReport(jasperReport, null,    con1);
   JasperViewer.viewReport(jasperPrint); 
   } catch (JRException e) {
        e.printStackTrace();
   } catch (ClassNotFoundException e) {
        e.printStackTrace();
   } catch (SQLException e) {
    e.printStackTrace();
 }
}

The output is:

ACCNAME     DR       CR
--------    ----    -----
CASH         1,200   0
AMRO BANK    0       500
CASH         0       600
AMRO BANK    700     0

I want to distinct and sum above and cut debt DR from credit CR and get result as following report :

ACCNAME     DR       CR
--------    ----    -----
CASH         600     0
AMRO BANK    200     0

Please assist in correcting select statement

Mohamed Bawaneen
  • 139
  • 1
  • 12

1 Answers1

2

yes I have got the correct select statement in SO, grouping the output in one nested package adding below select statement the reports working fine then just create a subquery to get the final step

SELECT ACCNAME,
CASE WHEN SUM(CREDIT - DEBT) < 0 THEN - SUM(CREDIT - DEBT)
ELSE 0
 END DEBT,
   CASE WHEN SUM(CREDIT - DEBT) > 0 THEN   SUM(CREDIT - DEBT)
        ELSE 0
   END CREDIT
FROM ( ...... ) myTable
GROUP BY ACCNAME ;

Thus i was got the right path .

Mohamed Bawaneen
  • 139
  • 1
  • 12