CODEWITHSUNDEEP
jasper-reports
Raman
Raman

Reputation: 19595

Using ternary expression inside a queryString

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.

Upvotes: 1

Views: 655

Answers (1)

Alex K
Alex K

Reputation: 22867

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:

Upvotes: 1

Related Questions