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