JasperReports: How to add a WHERE statement in SQL

Question

How can I add a sub where statement in SQL if my Boolean parameter is true in JasperReports?
For example, I have my SQL as below:

SELECT * FROM shops WHERE region = "Canada" ORDER BY name

If my parameter is true, I would like to add and isactive = 'Y' just before ORDER BY.
Anybody knows how I can achieve this?

Answer 1

You can add additional parameter for setting additional clause value. After that you can use $P!{} syntax in query.

The sample:

<parameter name="param" class="java.lang.Boolean"/>
<parameter name="whereCond" class="java.lang.String" isForPrompting="false">
    <defaultValueExpression><![CDATA[$P{param} ? " AND isactive='Y'" : ""]]></defaultValueExpression>
</parameter>
<queryString>
    <![CDATA[SELECT * FROM shops WHERE region='Canada' $P!{whereCond} ORDER BY name]]>
</queryString>

Answer 2

Try this

select * from shops where region = "Canada"
AND isActive = CASE WHEN @var = 'True' then 'Y' ELSE isActive END
order by name

Answer 3

You can do that direct in SQL

SELECT * FROM shops WHERE region = "Canada"
AND (@param <> `True` OR isActive = 'Y')
-----^^^^^^^^^^^^^^^^--<< Condition: Your param is not true------
ORDER BY name