Dynamic sql - error evaluating expression - works in studio but not server

0

Using Jaspersoft Studio Pro 7.5.0
Using Jasper Server Pro 7.5.1

Created parameter $P{DATE_RANGE} for use in dynamic sql statement

IF( $P{SPECIFY_TIME}.equals( "Y" ),
    "AND (dss.STAGED_TIMESTAMP>=TRUNC(TO_DATE('"+ $P{CUSTOM_DATE_FROM_OPT} +"','YYYY-MM-DD')) AND dss.STAGED_TIMESTAMP<TRUNC(TO_DATE('" +$P{CUSTOM_DATE_TO_OPT} + "','YYYY-MM-DD')))",
    ""
)

$P{CUSTOM_DATE_FROM / TO_OPT} is type java.sql.Date

DATE_RANGE is inserted in the SQL statement WHERE clause as --> WHERE ... $P!{DATE_RANGE} ...

The above works as expected in JasperSoft Studio, but when uploaded and run on Jasper Server, it produces error:

Error evaluating expression for source text: IF( $P{SPECIFY_TIME}.equals( "Y" ), "AND (dss.STAGED_TIMESTAMP>=TRUNC(TO_DATE('"+ $P{CUSTOM_DATE_FROM_OPT}+"','YYYY-MM-DD')) AND dss.STAGED_TIMESTAMP<TRUNC(TO_DATE('" +$P{CUSTOM_DATE_TO_OPT} + "','YYYY-MM-DD')))", "" )
net.sf.jasperreports.engine.fill.JRExpressionEvalException: Error evaluating expression for source text: IF( $P{SPECIFY_TIME}.equals( "Y" ), "AND (dss.STAGED_TIMESTAMP>=TRUNC(TO_DATE('"+ $P{CUSTOM_DATE_FROM_OPT}+"','YYYY-MM-DD')) AND dss.STAGED_TIMESTAMP<TRUNC(TO_DATE('" +$P{CUSTOM_DATE_TO_OPT} + "','YYYY-MM-DD')))", "" ) at net.sf.jasperreports.engine.fill.JREvaluator.handleEvaluationException(JREvaluator.java:284) at net.sf.jasperreports.engine.fill.JREvaluator.evaluate(JREvaluator.java:316) at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:673) at net.sf.jasperreports.engine.fill.JRCalculator.evaluate(JRCalculator.java:641) at ...

I've been able to run these types of dynamic sql with dates on community versions of Jasper server with no problem in the past.  Any thoughts to what might be going on?

johno_1's picture
Joined: Jul 8 2020 - 9:15am
Last seen: 2 days 17 hours ago

0 Answers:

No answers yet
Feedback