What is wrong in the query of the main Dataset ?

[aperez_6]

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 )
                                            DOCUMENTS
FROM
                COMUN.COM_DOM_VAL_TRADUCCIONES_VW T,
                COMUN.COM_DOM_VAL_TRADUCCIONES_VW T2,
                PREST.PRE_EJECS_OPERS_DOCUMENTOS D
WHERE
               ( 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,

 

[reportdev]

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. 

 

[aperez_6]

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.