Jasperreports: How To Add A Where Statement In Sql Query Depending On A Boolean Parameter?

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

Solution 1:

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

The sample:

<parametername="param"class="java.lang.Boolean"/><parametername="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>

Solution 2:

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------ORDERBY name

Solution 3:

Try this

select*from shops where region = "Canada"
AND isActive =CASEWHEN@var='True'then'Y'ELSE isActive ENDorderby name