org.pentaho.reporting.engine.classic.core.ReportDataFactoryException

Failed at query: SELECT * FROM ( SELECT CASE WHEN ${p_beg_sch_yr} >= 2013 THEN ' ' || CHR ( 64 + CASE WHEN aa.memb_adjust_type_id =8 THEN 1 WHEN aa.memb_adjust_type_id =9 THEN 2 WHEN aa.memb_adjust_type_id =10 THEN 3 WHEN aa.memb_adjust_type_id =11 THEN 4 WHEN aa.memb_adjust_type_id =1 THEN 5 WHEN aa.memb_adjust_type_id =12 THEN 6 WHEN aa.memb_adjust_type_id =6 THEN 7 WHEN aa.memb_adjust_type_id =37 THEN 8 WHEN aa.memb_adjust_type_id =16 THEN 9 WHEN aa.memb_adjust_type_id =17 THEN 10 WHEN aa.memb_adjust_type_id =35 THEN 11 WHEN aa.memb_adjust_type_id =38 THEN 12 END) || '. ' || aa.adj_descr WHEN ${p_beg_sch_yr} <= 2012 THEN ' ' || CHR ( 64 + CASE WHEN aa.memb_adjust_type_id =8 THEN 1 WHEN aa.memb_adjust_type_id =9 THEN 2 WHEN aa.memb_adjust_type_id =10 THEN 3 WHEN aa.memb_adjust_type_id =11 THEN 4 WHEN aa.memb_adjust_type_id =1 THEN 5 WHEN aa.memb_adjust_type_id =12 THEN 6 WHEN aa.memb_adjust_type_id =6 THEN 7 WHEN aa.memb_adjust_type_id =16 THEN 8 WHEN aa.memb_adjust_type_id =17 THEN 9 WHEN aa.memb_adjust_type_id =35 THEN 10 END) || '. ' || aa.adj_descr ELSE aa.adj_descr END k3_deduct_adj_descr, deduct_cnt k3_deduct_cnt, aa.grade_code k3_deduct_grade, (CASE WHEN ${p_beg_sch_yr} >= 2013 THEN (CASE WHEN aa.memb_adjust_type_id =8 THEN 1 WHEN aa.memb_adjust_type_id =9 THEN 2 WHEN aa.memb_adjust_type_id =10 THEN 3 WHEN aa.memb_adjust_type_id =11 THEN 4 WHEN aa.memb_adjust_type_id =1 THEN 5 WHEN aa.memb_adjust_type_id =12 THEN 6 WHEN aa.memb_adjust_type_id =6 THEN 7 WHEN aa.memb_adjust_type_id =37 THEN 8 WHEN aa.memb_adjust_type_id =16 THEN 9 WHEN aa.memb_adjust_type_id =17 THEN 10 WHEN aa.memb_adjust_type_id =35 THEN 11 WHEN aa.memb_adjust_type_id =38 THEN 12 END) WHEN ${p_beg_sch_yr} <= 2012 THEN (CASE WHEN aa.memb_adjust_type_id =8 THEN 1 WHEN aa.memb_adjust_type_id =9 THEN 2 WHEN aa.memb_adjust_type_id =10 THEN 3 WHEN aa.memb_adjust_type_id =11 THEN 4 WHEN aa.memb_adjust_type_id =1 THEN 5 WHEN aa.memb_adjust_type_id =12 THEN 6 WHEN aa.memb_adjust_type_id =6 THEN 7 WHEN aa.memb_adjust_type_id =16 THEN 8 WHEN aa.memb_adjust_type_id =17 THEN 9 WHEN aa.memb_adjust_type_id =35 THEN 10 END) END) k3_deduct_orderby FROM (SELECT DECODE ( memb_adjust_type_id, 1, 'Pupils for whom you receive tuition from another school division in Virginia', amat.descr) adj_descr, memb_adjust_type_id, grade_code, stu_rec_sort_seq FROM school.memb_adjust_type amat, grade gc WHERE amat.memb_adjust_type_id IN (1, 6, 8, 9, 10, 11, 12, 16, 17, 35, 37, 38) AND 1 = (CASE WHEN ${p_beg_sch_yr} = 2012 AND amat.memb_adjust_type_id IN (37,38) THEN 0 WHEN ${p_beg_sch_yr} = 2011 AND amat.memb_adjust_type_id IN (37,38) THEN 0 WHEN ${p_beg_sch_yr} >= 2010 AND amat.memb_adjust_type_id IN (35,37,38) THEN 0 ELSE 1 END) AND gc.grade_code IN ('KG', '01', '02', '03')) aa LEFT OUTER JOIN ( SELECT SUM (stu_cnt) deduct_cnt, memb_adjust_type_id, DECODE (grade_code, 'KA', 'KG', 'KP', 'KG', 'T1', '01', grade_code) g_code FROM k3_memb_adjustment WHERE beg_sch_yr = ${p_beg_sch_yr} AND memb_count_type_id = 5 AND responsible_edagency_num NOT IN (SELECT contract_edagency_num FROM ed_agency_contract WHERE reporting_edagency_num = ${p_edagency_num}) AND DECODE (memb_adjust_type_id, 1, serving_edagency_num, reporting_edagency_num) = ${p_edagency_num} GROUP BY memb_adjust_type_id, DECODE (grade_code, 'KA', 'KG', 'KP', 'KG', 'T1', '01', grade_code)) ama ON ( aa.memb_adjust_type_id = ama.memb_adjust_type_id AND aa.grade_code = ama.g_code) LEFT OUTER JOIN ( SELECT SUM (stu_cnt) c_deduct_cnt, memb_adjust_type_id, grade_code c_g_code FROM k3_memb_adjustment WHERE beg_sch_yr = ${p_beg_sch_yr} AND memb_count_type_id = 5 AND responsible_edagency_num IN (SELECT contract_edagency_num FROM ed_agency_contract WHERE reporting_edagency_num = ${p_edagency_num}) AND DECODE (memb_adjust_type_id, 1, serving_edagency_num, reporting_edagency_num) = ${p_edagency_num} GROUP BY memb_adjust_type_id, grade_code) ama_c ON ( aa.memb_adjust_type_id = ama_c.memb_adjust_type_id AND aa.grade_code = ama_c.c_g_code) ORDER BY k3_deduct_orderby, stu_rec_sort_seq ) PIVOT (SUM (NVL (k3_deduct_cnt, 0)) FOR k3_deduct_grade IN ('KG','01','02','03')) ORDER BY 2

Samebug tips1

This is a bug in version 4.1.9 and it's fixed from version 4.3.0beta and beyond.

Don't give up yet. Our experts can help. Paste your full stack trace to get a solution.

Solutions on the web80

  • via Pentaho BI Platform Tracking by Ramesh, 1 year ago
    , grade gc WHERE amat.memb_adjust_type_id IN (1, 6, 8, 9, 10, 11, 12, 16, 17, 35, 37, 38) AND 1 = (CASE WHEN
  • via Google Groups by Unknown author, 1 month ago
    Failed at query: select * from t_dim_article_youhui_guonei
  • via Pentaho BI Platform Tracking by David Kincade, 1 year ago
    Failed at query: select foo from bar
  • Stack trace

    • org.pentaho.reporting.engine.classic.core.ReportDataFactoryException: Failed at query: SELECT * FROM ( SELECT CASE WHEN ${p_beg_sch_yr} >= 2013 THEN ' ' || CHR ( 64 + CASE WHEN aa.memb_adjust_type_id =8 THEN 1 WHEN aa.memb_adjust_type_id =9 THEN 2 WHEN aa.memb_adjust_type_id =10 THEN 3 WHEN aa.memb_adjust_type_id =11 THEN 4 WHEN aa.memb_adjust_type_id =1 THEN 5 WHEN aa.memb_adjust_type_id =12 THEN 6 WHEN aa.memb_adjust_type_id =6 THEN 7 WHEN aa.memb_adjust_type_id =37 THEN 8 WHEN aa.memb_adjust_type_id =16 THEN 9 WHEN aa.memb_adjust_type_id =17 THEN 10 WHEN aa.memb_adjust_type_id =35 THEN 11 WHEN aa.memb_adjust_type_id =38 THEN 12 END) || '. ' || aa.adj_descr WHEN ${p_beg_sch_yr} <= 2012 THEN ' ' || CHR ( 64 + CASE WHEN aa.memb_adjust_type_id =8 THEN 1 WHEN aa.memb_adjust_type_id =9 THEN 2 WHEN aa.memb_adjust_type_id =10 THEN 3 WHEN aa.memb_adjust_type_id =11 THEN 4 WHEN aa.memb_adjust_type_id =1 THEN 5 WHEN aa.memb_adjust_type_id =12 THEN 6 WHEN aa.memb_adjust_type_id =6 THEN 7 WHEN aa.memb_adjust_type_id =16 THEN 8 WHEN aa.memb_adjust_type_id =17 THEN 9 WHEN aa.memb_adjust_type_id =35 THEN 10 END) || '. ' || aa.adj_descr ELSE aa.adj_descr END k3_deduct_adj_descr, deduct_cnt k3_deduct_cnt, aa.grade_code k3_deduct_grade, (CASE WHEN ${p_beg_sch_yr} >= 2013 THEN (CASE WHEN aa.memb_adjust_type_id =8 THEN 1 WHEN aa.memb_adjust_type_id =9 THEN 2 WHEN aa.memb_adjust_type_id =10 THEN 3 WHEN aa.memb_adjust_type_id =11 THEN 4 WHEN aa.memb_adjust_type_id =1 THEN 5 WHEN aa.memb_adjust_type_id =12 THEN 6 WHEN aa.memb_adjust_type_id =6 THEN 7 WHEN aa.memb_adjust_type_id =37 THEN 8 WHEN aa.memb_adjust_type_id =16 THEN 9 WHEN aa.memb_adjust_type_id =17 THEN 10 WHEN aa.memb_adjust_type_id =35 THEN 11 WHEN aa.memb_adjust_type_id =38 THEN 12 END) WHEN ${p_beg_sch_yr} <= 2012 THEN (CASE WHEN aa.memb_adjust_type_id =8 THEN 1 WHEN aa.memb_adjust_type_id =9 THEN 2 WHEN aa.memb_adjust_type_id =10 THEN 3 WHEN aa.memb_adjust_type_id =11 THEN 4 WHEN aa.memb_adjust_type_id =1 THEN 5 WHEN aa.memb_adjust_type_id =12 THEN 6 WHEN aa.memb_adjust_type_id =6 THEN 7 WHEN aa.memb_adjust_type_id =16 THEN 8 WHEN aa.memb_adjust_type_id =17 THEN 9 WHEN aa.memb_adjust_type_id =35 THEN 10 END) END) k3_deduct_orderby FROM (SELECT DECODE ( memb_adjust_type_id, 1, 'Pupils for whom you receive tuition from another school division in Virginia', amat.descr) adj_descr, memb_adjust_type_id, grade_code, stu_rec_sort_seq FROM school.memb_adjust_type amat, grade gc WHERE amat.memb_adjust_type_id IN (1, 6, 8, 9, 10, 11, 12, 16, 17, 35, 37, 38) AND 1 = (CASE WHEN ${p_beg_sch_yr} = 2012 AND amat.memb_adjust_type_id IN (37,38) THEN 0 WHEN ${p_beg_sch_yr} = 2011 AND amat.memb_adjust_type_id IN (37,38) THEN 0 WHEN ${p_beg_sch_yr} >= 2010 AND amat.memb_adjust_type_id IN (35,37,38) THEN 0 ELSE 1 END) AND gc.grade_code IN ('KG', '01', '02', '03')) aa LEFT OUTER JOIN ( SELECT SUM (stu_cnt) deduct_cnt, memb_adjust_type_id, DECODE (grade_code, 'KA', 'KG', 'KP', 'KG', 'T1', '01', grade_code) g_code FROM k3_memb_adjustment WHERE beg_sch_yr = ${p_beg_sch_yr} AND memb_count_type_id = 5 AND responsible_edagency_num NOT IN (SELECT contract_edagency_num FROM ed_agency_contract WHERE reporting_edagency_num = ${p_edagency_num}) AND DECODE (memb_adjust_type_id, 1, serving_edagency_num, reporting_edagency_num) = ${p_edagency_num} GROUP BY memb_adjust_type_id, DECODE (grade_code, 'KA', 'KG', 'KP', 'KG', 'T1', '01', grade_code)) ama ON ( aa.memb_adjust_type_id = ama.memb_adjust_type_id AND aa.grade_code = ama.g_code) LEFT OUTER JOIN ( SELECT SUM (stu_cnt) c_deduct_cnt, memb_adjust_type_id, grade_code c_g_code FROM k3_memb_adjustment WHERE beg_sch_yr = ${p_beg_sch_yr} AND memb_count_type_id = 5 AND responsible_edagency_num IN (SELECT contract_edagency_num FROM ed_agency_contract WHERE reporting_edagency_num = ${p_edagency_num}) AND DECODE (memb_adjust_type_id, 1, serving_edagency_num, reporting_edagency_num) = ${p_edagency_num} GROUP BY memb_adjust_type_id, grade_code) ama_c ON ( aa.memb_adjust_type_id = ama_c.memb_adjust_type_id AND aa.grade_code = ama_c.c_g_code) ORDER BY k3_deduct_orderby, stu_rec_sort_seq ) PIVOT (SUM (NVL (k3_deduct_cnt, 0)) FOR k3_deduct_grade IN ('KG','01','02','03')) ORDER BY 2 at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:210) at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SQLReportDataFactory.queryData(SQLReportDataFactory.java:162) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStaticInternal(CompoundDataFactory.java:205) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:182) at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryInternal(CachingDataFactory.java:505) at org.pentaho.reporting.engine.classic.core.cache.CachingDataFactory.queryStatic(CachingDataFactory.java:181) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStaticInternal(CompoundDataFactory.java:200) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:182) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStaticInternal(CompoundDataFactory.java:200) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:182) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStaticInternal(CompoundDataFactory.java:200) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:182) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStaticInternal(CompoundDataFactory.java:200) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryStatic(CompoundDataFactory.java:182) at org.pentaho.reporting.engine.classic.core.CompoundDataFactory.queryData(CompoundDataFactory.java:69) at org.pentaho.reporting.engine.classic.core.states.datarow.DefaultFlowController.performQueryData(DefaultFlowController.java:296) at org.pentaho.reporting.engine.classic.core.states.datarow.DefaultFlowController.performSubReportQuery(DefaultFlowController.java:374) at org.pentaho.reporting.engine.classic.core.states.process.ProcessState.initializeForSubreport(ProcessState.java:596) at org.pentaho.reporting.engine.classic.core.states.process.EndSubReportHandler.commit(EndSubReportHandler.java:59) at org.pentaho.reporting.engine.classic.core.states.process.ProcessState.commit(ProcessState.java:1059) at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.processPrepareLevels(AbstractReportProcessor.java:437) at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.prepareReportProcessing(AbstractReportProcessor.java:558) at org.pentaho.reporting.engine.classic.core.layout.output.AbstractReportProcessor.processReport(AbstractReportProcessor.java:1713) at org.pentaho.reporting.designer.core.actions.report.preview.PreviewPdfAction$ExportTask.run(PreviewPdfAction.java:123) at java.lang.Thread.run(Thread.java:745) Caused by: java.sql.SQLException: ORA-01000: maximum open cursors exceeded at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:225) at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:53) at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:774) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:925) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1111) at oracle.jdbc.driver.OracleStatement.doScrollExecuteCommon(OracleStatement.java:5179) at oracle.jdbc.driver.OraclePreparedStatement.doScrollPstmtExecuteUpdate(OraclePreparedStatement.java:12485) at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4793) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501) at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.parametrizeAndQuery(SimpleSQLReportDataFactory.java:379) at org.pentaho.reporting.engine.classic.core.modules.misc.datafactory.sql.SimpleSQLReportDataFactory.queryData(SimpleSQLReportDataFactory.java:206) ... 24 more

    Write tip

    You have a different solution? A short tip here would help you and many other users who saw this issue last week.

    Users with the same issue

    Unknown user
    Once, 1 year ago
    Unknown user
    Once, 1 day ago
    Unknown user
    Once, 1 day ago
    Unknown user
    Once, 1 day ago
    Unknown user
    Once, 1 day ago
    162 more bugmates