Using ternary expression inside a queryString

Question

I have a JasperReports 6.3 report query which uses a "start date" parameter as part of its WHERE clause. The parameter may be null, which essentially means "earliest date".

The logic is very simple:

WHERE MyDateField > ($P{StartDate} == null ? DateTime(0L) : $P{StartDate})

But this produces an exception when JasperReports tries to run the query against the database:

SQLServerException: The value is not set for the parameter number 3

As a side note, it would be useful to figure out how to get Jaspersoft Studio to log the generated SQL, to assist in debugging.

Answer 1

It is impossible to use expressions at queryString. You should create another one parameter contains the expression you need.

In your case the code will be like this:

<parameter name="StartDate" class="org.joda.time.DateTime"/>
...
<parameter name="dateFilter" class="org.joda.time.DateTime">
    <defaultValueExpression><![CDATA[$P{StartDate} == null ? DateTime(0L) : $P{StartDate}]]></defaultValueExpression>
</parameter>
<queryString>
    <![CDATA[SELECT * FROM sometable WHERE MyDateField > $P{dateFilter}]]>
</queryString>

---

You can find more information about using parameters here:

• JasperReports: Passing parameters to query