aperez_6 Posted December 28, 2018 Share Posted December 28, 2018 Good afternoon, I have a problem when execute a jasper, at the moment that a one parameter is null. The query in the main Dataset is: SELECT DISTINCT DECODE( NVL($P{p_idreg_docs_prest},'0'), '0', NULL, CASE WHEN T2.TRA_TRADUCCION IS NOT NULL OR D.EJERCICIO IS NOT NULL THEN (T.TRA_TRADUCCION||' -> '||T2.TRA_TRADUCCION||' '||D.EJERCICIO) ELSE T.TRA_TRADUCCION END ) DOCUMENTSFROM COMUN.COM_DOM_VAL_TRADUCCIONES_VW T, COMUN.COM_DOM_VAL_TRADUCCIONES_VW T2, PREST.PRE_EJECS_OPERS_DOCUMENTOS DWHERE ( D.IDREG IN ( $P!{p_idreg_docs_prest}) and $P{p_idreg_docs_prest} is not null or $P{p_idreg_docs_prest} is null ) AND T.VAL_IDREG=D.IDDO_MODELO_INFORMACION AND T2.VAL_IDREG(+) = D.IDDO_PERIODO AND T.TRA_IDDO_IDIOMA = $P{p_idioma} AND T2.TRA_IDDO_IDIOMA(+) = $P{p_idioma} When the parameter p_idreg_docs_prest IS NOT NULL, works wonderfully. But if the parameter is null, goes out the following message: net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Reclamacion_documentos_simple. at com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:548) at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$20(ReportControler.java:523) at com.jaspersoft.studio.editor.preview.view.control.ReportControler$5.run(ReportControler.java:404) at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Reclamacion_documentos_simple. at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:344) at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1245) at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:723) at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:438) at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:550) at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123) at java.lang.Thread.run(Thread.java:745)Caused by: java.sql.SQLSyntaxErrorException: ORA-00936: missing an expression 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.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798) at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:4845) at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1501) at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310) ... 6 more With the statement $X{ IN, FIELD, PARAMETER }, doesn't works too. Maybe I have to change a property in the TextField ( DOCUMENTS ) or make an Print When Expression?If someone has had this problem, I would appreciate reporting the solution.Thank you, Link to comment Share on other sites More sharing options...
reportdev Posted December 29, 2018 Share Posted December 29, 2018 In order to initiate the resultset, dummy parameter is needed in this case. Create another parameter dependent upon p_idreg_docs_prest, called as p_idreg_docs_prest_handler.p_idreg_docs_prest_handler code : Use a terinary expression to evaluate if p_idreg_docs_prest is null (may be like $P{p_idreg_docs_prest}.equals(null) ? "" : $P{p_idreg_docs_prest} )Now instead of using p_idreg_docs_prest parameter in your SQL query, use p_idreg_docs_prest_handler value as $P!{p_idreg_docs_prest_handler}. In this case a string value will replace the parameter. I believe it should work this way. Link to comment Share on other sites More sharing options...
aperez_6 Posted January 8, 2019 Author Share Posted January 8, 2019 I have to resume it after the holidays and see the situation in which I leave it. When I try it, I will comment on the result.Thanks for the reply. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now